Syntax Error - Insert Column Header (Good), Insert Formula in Row 2 (Good), Drag to Last Row (ERROR)

[jwb1012]

Hello, any ideas how I can get the following code to work? I am adding a column called "Value less VAT", then adding in the formula in row 2, then attempting to drag it down to the last row. Everything works like a charm, except the dragging down portion. See details below.

Code #1 = my code that is resulting in a syntax error
Code #2 = code that works properly, EXCEPT I need the row # to be dynamic (i.e. instead of being P2:AF2 in all rows, I need it to be P2:AF2 in row 2, then P3:AF3 in row 3, etc...)

Sub Loop_Example()
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim LastColumn As Integer
    Dim LastRow As Integer
    
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Select "Inventory Transaction Summary" worksheet
    With Sheets("Inventory Transaction Summary -")
        'Improves speed of macro
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False
        
    'Insert "Value less VAT" column heading after last column
    LastColumn = ActiveSheet.UsedRange.Columns.Count
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Cells(1, LastColumn + 1) = "Value Less VAT"
    
    For n = LastRow To 2 Step -1
    ActiveSheet.Cells(n, LastColumn + 1) = "="P" & n * "AF" & n"
    
    Next n
    
    End With
    
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

Code #2:

Sub Loop_Example()
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim LastColumn As Integer
    Dim LastRow As Integer
    
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Select "Inventory Transaction Summary" worksheet
    With Sheets("Inventory Transaction Summary -")
        'Improves speed of macro
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False
        
    'Insert "Value less VAT" column heading after last column
    LastColumn = ActiveSheet.UsedRange.Columns.Count
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Cells(1, LastColumn + 1) = "Value Less VAT"
    
    For n = LastRow To 2 Step -1
    ActiveSheet.Cells(n, LastColumn + 1) = "=P2*AF2"
    
    Next n
    
    End With
    
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

 

[steve the fish]

Replace that loop with:

Range(Cells(2, Lastcolumn + 1), Cells(LastRow, Lastcolumn + 1)).FormulaR1C1 = "=RC16*RC32"

 

[jwb1012]

Thank you! It works perfectly.

Have a nice day!