CODEWITHSUNDEEP
vbaexcel
gr33ns
gr33ns

Reputation: 83

Adding characters to a cell and autofilling the column in VBA

I'd like to add a few characters the values in column A, then having it automatically fill down the rest of the rows that have values. I've tried a couple variations of code I've found in other threads and gotten the same: Run time error 1004: Method range object _global failed.

Here are the codes I've tried:

Range("B1:B" & LastRow).Formula = "=CHAR(34)&A1&CHAR(34)&CHAR(44)"

and

Range("B1") = "=CHAR(34)&A1&CHAR(34)&CHAR(44)": Range("B1:B" & LastRow).FillDown

Upvotes: 1

Views: 344

Answers (1)

user6432984
user6432984

Reputation:

Run time error 1004: Method range object _global failed will be thrown by the value of LastRow being < 1. You can usually avoid using the LastRow variable by using the coding patterns below. 

With Worksheets("Sheet1")
    .Range("B1", .Range("B" & .Rows.Count).End(xlUp)).Formula = "=CHAR(34)&A1&CHAR(34)&CHAR(44)"
End With

@mooseman brings up a good point.

If you need to get cells based of the last row of another column then you can use Range.Offset([col],[row])

With Worksheets("Sheet1")
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Offset(0, 1).Formula = "=CHAR(34)&A1&CHAR(34)&CHAR(44)"
End With

This method ensures that you are tartgeing the correct column.

With Worksheets("Sheet1")
    With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
       .EntireRow.Columns("B").Formula = "=CHAR(34)&A1&CHAR(34)&CHAR(44)"
    End With
End With

Upvotes: 2

Related Questions