Q:

Autofill method of Range class failed: VBA

I'm new to VBA and I'm having trouble understanding where my code is falling down. I receive the message "Autofill method of Range class failed" after trying to implement an If statement.
My code is:
 Columns("H:H").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(C[-6] = ""Commodities Ags/Softs"", (IF(RC[-3]=R1C24,""Y"",(IF(RC[-3]=R2C24,""Y"",(IF(RC[-3]=R3C24,""Y"",(IF(RC[-3]=R4C24,""Y"",(IF(RC[-3]=R5C24,""Y"",(IF(RC[-3]=R6C24,""Y"",(IF(RC[-3]=R7C24,""Y"",(IF(RC[-3]=R8C24,""Y"",(IF(RC[-3]=R9C24,""Y"",""N"")))))))))))))))))),"""")"
Selection.AutoFill Destination:=Columns("H:H"), Type:=xlFillDefault

It fails at the last line, the If statement is fine, any help would be greatly appreciated,
Cheers

A:

Is this what you are trying?
Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find last row in Col H
        lRow = .Range("H" & .Rows.Count).End(xlUp).Row

        '~~> Enter the formula in one go
        .Range("H1:H" & lRow).FormulaR1C1 = "=IF(C[-6] = ""Commodities Ags/Softs"", " & _
                                            "(IF(RC[-3]=R1C24,""Y"",(IF(RC[-3]=R2C24,""Y""," & _
                                            "(IF(RC[-3]=R3C24,""Y"",(IF(RC[-3]=R4C24,""Y""," & _
                                            "(IF(RC[-3]=R5C24,""Y"",(IF(RC[-3]=R6C24,""Y""," & _
                                            "(IF(RC[-3]=R7C24,""Y"",(IF(RC[-3]=R8C24,""Y""," & _
                                            "(IF(RC[-3]=R9C24,""Y"",""N"")))))))))))))))))),"""")"
    End With
End Sub

