loop VBA

[JimmyJammy]

Hi, i'm a beginner to excel/vba and a little stuck on how to create the following using a loop command.

At present my VBA has list (long) of instructions;

Range("B3").Select
ActiveCell.Formula = "=SUM(Sheet1!C5-Sheet1!C4)"
Range("B4").Select
ActiveCell.Formula = "=SUM(Sheet1!E5-Sheet1!E4)"
Range("B5").Select
ActiveCell.Formula = "=SUM(Sheet1!G5-Sheet1!G4)"
Range("B6").Select
ActiveCell.Formula = "=SUM(Sheet1!I5-Sheet1!I4)"
Range("B7").Select
ActiveCell.Formula = "=SUM(Sheet1!K5-Sheet1!K4)"
Range("B8").Select
etc

Any help or advice would be greatly appreciated.

Thanks,

Jim

 

[Alex Blakenburg]

Here is my version.
You only need the references to "Sheet1!" if your references to the Column B are not on Sheet 1.
Without naming the sheet its going to assume the active sheet.

Sub FormulaLoop()

    Dim sumCol As Long
    Dim resultRowFirst As Long
    Dim resultRowLast As Long
    Dim iRow As Long
    
    resultRowFirst = 3
    resultRowLast = 10
    sumCol = 3
    
    For iRow = resultRowFirst To resultRowLast
    
        Range("B" & iRow).Formula = "=SUM(Sheet1!" & Cells(5, sumCol).Address(False, False) & " - Sheet1!" & Cells(4, sumCol).Address(False, False) & ")"
        sumCol = sumCol + 2
        
    Next iRow

End Sub

 

[JimmyJammy]

That's amazing, thanks for you swift response.

 

[Alex Blakenburg]

No problem. Happy to help.