Fill columns with formulas in a loop

Question

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

Are "R1C1" formulae a possible answer?

Dominique
– Dominique

2022-09-23 10:53:05 +00:00
Commented Sep 23, 2022 at 10:53 — Dominique
– Dominique, Commented Sep 23, 2022 at 10:53

ouroboros1 · Accepted Answer · 2022-09-23 10:16:30Z

1

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

edited Sep 23, 2022 at 10:16

answered Sep 23, 2022 at 10:05

ouroboros1

15.2k7 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fill columns with formulas in a loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related