VBA AutoFill Code (2002 SP-2)

Topic

New Reply

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:
[indent]

---

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

---

[/indent]Thanks,
Becky

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

Try this

 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

Reply | Quote

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

Reply | Quote

Try replacing:

    Range("A1").AutoFill Range(Range("a1"), _ 
       Range(strLastCell ).End(xlUp).Offset(0, -1))

with

Range("A1").AutoFill Range(Range("a1"), Range(strLastCell))

Cheers

Reply | Quote

Try replacing:

    Range("A1").AutoFill Range(Range("a1"), _ 
       Range(strLastCell ).End(xlUp).Offset(0, -1))

with

Range("A1").AutoFill Range(Range("a1"), Range(strLastCell))

Cheers

Reply | Quote

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

Reply | Quote

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

Reply | Quote

In your case there is no real need to call upon the AutoFill method. You could try

    Columns("A:A").Insert Shift:=xlToRight
    Range(Range("A1"), Range("B65536").End(xlUp).Offset(0, -1)) = "A"

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

In your case there is no real need to call upon the AutoFill method. You could try

    Columns("A:A").Insert Shift:=xlToRight
    Range(Range("A1"), Range("B65536").End(xlUp).Offset(0, -1)) = "A"

Andrew C

Reply | Quote

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

Reply | Quote

Try this

 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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Is this what you are after?

Steve

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

Reply | Quote

Thank you!! It works wonderfully!

Reply | Quote

Thank you!! It works wonderfully!

Reply | Quote

Is this what you are after?

Steve

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

Reply | Quote