Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA AutoFill Code (2002 SP-2)

    I have an Excel spreadsheet that runs a macro on open. I am attempting to open a csv file, add a column, fill that column in to the end of the other columns with the letter "A", then save the file back as a CSV. Everything is working properly, except I don't know how to tell it to fill the "A"'s down to the end of the other rows. Any suggestions? Here's what I've got so far:
    <hr>Private Sub Workbook_Open()
    Dim objXLBook As Excel.Workbook
    Set objXLBook = Workbooks.Open("H:equitracrightfaxcodechg.csv")
    With Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "a"
    Range("A1").Select
    Selection.Copy
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    End With
    End Sub<hr>
    Thanks,
    Becky

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Sorry I am not clear on what you are trying to do.
    "fill that column in to the end of the other columns with the letter "A""

    Did you mean, fill that column to the end of the other rows? Or are you trying to replace the existing data in every column?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Is this what you are after?

    Steve

    <pre>Private Sub Workbook_Open()
    Dim objXLBook As Excel.Workbook
    Set objXLBook = Workbooks.Open("H:equitracrightfaxcodechg.csv")
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1").FormulaR1C1 = "a"
    Range("A1").AutoFill Range(Range("a1"), _
    Range("B65536").End(xlUp).Offset(0, -1))
    ActiveWorkbook.Save
    End Sub</pre>


  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Yes, fill that column (the new column A) to the end of the other rows. If the row has an "A" in the first cell, it tells the program that looks at the file to Add that entry to the database. I need to add an A to the beginning of every row, but I don't want the A's going on forever and creating rows with nothing but A's (the rows with the A's must have other data in them).

    I'm sorry if I'm making this more confusing than it needs to be!

    Becky

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Thank you!! It works wonderfully! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Try this
    <pre> Private Sub Workbook_Open()
    Dim objXLBook As Excel.Workbook
    Dim intLastRow As Integer
    Dim strLastCell as String
    Set objXLBook = Workbooks.Open("H:equitracrightfaxcodechg.csv")
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1").FormulaR1C1 = "a"
    'find the last row in sheet
    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Activate
    intLastRow = ActiveCell.Row
    strLastCell = "A" & intLastRow
    Range("A1").AutoFill Range(Range("a1"), _
    Range(strLastCell ).End(xlUp).Offset(0, -1))
    ActiveWorkbook.Save
    End Sub
    </pre>

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Thanks for the code; I really appreciate the help. It gave me an "Application defined or object defined error" when I ran it. It didn't point me to the line that gave the error, so I'm not sure where the problem is. Steve's works, so I'll just use his, unless yours is better.

    Thanks again!
    Becky

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    Try replacing:
    <pre> Range("A1").AutoFill Range(Range("a1"), _
    Range(strLastCell ).End(xlUp).Offset(0, -1))</pre>


    with

    <pre>Range("A1").AutoFill Range(Range("a1"), Range(strLastCell))</pre>


    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    I think the line:
    Range("A1").AutoFill Range(Range("a1"), _
    Range(strLastCell ).End(xlUp).Offset(0, -1))

    Should be:
    Range("A1").AutoFill Range(Range("a1"), Range(strLastCell))

    Range(strLastCell ) = The is A of the last row
    Range(strLastCell ).End(xlUp) gets you to A1
    Range(strLastCell ).End(xlUp).Offset(0, -1) tries to go 1 col to the left, giving an error since it is off the sheet.

    Catherine's code will fill A as far down as the last row used in the entire sheet. Mine will only go as far as the last row in col B. Unlike the manual autofill, neither method will be hindered by a blank item in the list.

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    In your case there is no real need to call upon the AutoFill method. You could try <pre> Columns("A:A").Insert Shift:=xlToRight
    Range(Range("A1"), Range("B65536").End(xlUp).Offset(0, -1)) = "A"</pre>


    Andrew C

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA AutoFill Code (2002 SP-2)

    I had figured in the "real code", that a formula was going in (the .formular1c1 property was used not the value property) and that the "a" was only a demo.

    If my presumption is incorrect, then your code is better.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •