CODEWITHSUNDEEP
vbaexcel
christopherhlee
christopherhlee

Reputation: 99

Autofill won't start at desired cell and won't fill down

I'm trying to autofill this formula from AD2 down to the end of the dataset. But, instead, my macro will use the formula on AD1 (the column title) and not fill down. I've done this several times, but I can't figure out why it's acting up now. The obnoxious formula is reading the from the cell a few columns over (AB) and then declares one of three strings.

Dim lastRow As Long
lastRow = Cells(Rows.Count).End(xlUp).Row

Range("AD2").Select
Selection.FormulaR1C1 = _
    "=IF(NOT(ISERROR(FIND(""iMac"",RC[-2]))),""iMac"",IF(NOT(ISERROR(FIND(""MacBook"",R[-21]C[-2]))),""MacBook"",""N/A""))"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD" & lastRow)

Upvotes: 1

Views: 227

Answers (2)

JC Guidicelli
JC Guidicelli

Reputation: 1316

try to modify your var lastRow with ActiveSheet.Cells(ActiveSheet.Rows.Count, "AB").End(xlUp).Row

Sub test()


Dim lastRow As Long
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "AB").End(xlUp).Row

Range("AD2").Select
Selection.FormulaR1C1 = _
    "=IF(NOT(ISERROR(FIND(""iMac"",RC[-2]))),""iMac"",IF(NOT(ISERROR(FIND(""MacBook"",R[-21]C[-2]))),""MacBook"",""N/A""))"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD" & lastRow)

End Sub

Upvotes: 2

SJR
SJR

Reputation: 23081

Try this. You are missing a column in your Cells (I have used column A so change to suit) and you don't need to select anything. In fact you probably don't need Autofill at all, just apply to the whole range in one go.

Sub y()

Dim lastRow As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

With Range("AD2")
    .FormulaR1C1 = _
        "=IF(NOT(ISERROR(FIND(""iMac"",RC[-2]))),""iMac"",IF(NOT(ISERROR(FIND(""MacBook"",R[-21]C[-2]))),""MacBook"",""N/A""))"
    .AutoFill Destination:=Range("AD2:AD" & lastRow)
End With

End Sub

Upvotes: 0

Related Questions