Fill columns with formulas in a loop-CodePudding

how can I fill the entire row (or to given column) with formulas that will use values from same column but different rows in VBA? That is I want to fill a range lets say from column B to column D at row 6 with formulas: =SUM(B4:B5), =SUM(C4:C5), =SUM(D4:D5) Because my number of columns will varry I can't make it static If it was row wise not column wise I would do:

For row = 1 To 10
   ActiveSheet.Range("B" & row).Formula =  "=SUM(C" & row & "D" & row & ")"
Next row

but I don't know how can I do that by changing column (letter). I was thinking about making an array filled with letters as

myColumns = Array("A", "B", ..., "ZZ")

and then loop through it, but writing such long array might be kind of pathetic :v

CodePudding user response：

You don't need a loop for this. Try:

Sub fill_formula()

With Range("B6:J6")
    .Formula = "=SUM(B4:B5)"
End With

End Sub

This will populate B6 with =SUM(B4:B5), C6 with =SUM(C4:C5) and so on through to column J. This works because the cell references are relative (e.g. B4, not $B$4).

If you really want a loop, try using Worksheet.Cells with Range.Address.

Sub fill_formula_loop()

For col = 2 To 10
    address_start = ActiveSheet.Cells(4, col).Address(False, False)
    address_end = ActiveSheet.Cells(5, col).Address(False, False)
    
    ActiveSheet.Cells(6, col).Formula = "=SUM(" & address_start & ":" & address_end & ")"
Next col

End Sub