Excel VBA formula with variables

Question

I would like to generate a formula in my code, say for example:

Range("L2:L17).Formula="=SUMIF($A$2:$A$17,A2,G2:G17)"

Except, I would like L, G, and A to be variables that are decided by some other factors earlier in the macro. Similarly, I would also like 2 and 17 to be replaced by two variables, whose specific values are determined earlier in the code.

So something like

Range("var1 j: var1 k).Formula="=SUMIF($ var2 $ j : $ var2 $ k, var2 j, var3 j : var3 k)"

I guess I could convert these variables to string and just do a string concatenate but I am not able to do it just yet. Everything I try gives errors.

Range(foldtot_add & "2:"& foldtot_add & FinalRow).Formula="=SUMIF("$"&FolderId_add & "$2:" & "$"& FolderId_add & FinalRow, FolderId_add &" 2", daycountcol2 & "2 :" & daycountcol2 & FinalRow)"

Where foldtot_add, FolderId_add, and daycountcol2 are names of my columns and FinalRow is the name of a row.

Answer 1

In the case you need variables for columns too, you could reference the cell(s) using the row and column number; then, get the address and populate that in your formula. The example below sets the row start, row end, and the column numbers, and then gets the address for each range

Dim rowStart as Integer
Dim rowEnd as Integer
Dim firstCol as Integer
Dim secondCol as Integer
Dim thirdCol as Integer
Dim aAddress as String
Dim gAddress as String
Dim lAddress as String
Dim aCell as String

rowStart = 2
rowEnd = 17
firstCol = 1
secondCol = 7
thirdCol = 12

' This gives you "$A$2:$A$17"
aAddress = Cells(rowStart, firstCol).Address & ":" & _
                    Cells(rowEnd, firstCol).Address

' This gives you "G2:G17"
gAddress = Cells(rowStart, secondCol).Address & ":" & _
                    Cells(rowEnd, secondCol).Address(RowAbsolute:=False, _
                                                     ColumnAbsolute:=False)

' This gives you "L2:L17"
lAddress = Cells(rowStart, thirdCol).Address & ":" & _
                    Cells(rowEnd, thirdCol).Address(RowAbsolute:=False, _
                                                     ColumnAbsolute:=False)

' This gives you "A2"
aCell = Cells(rowStart, firstCol).Address(RowAbsolute:=False, _
                                                     ColumnAbsolute:=False)

' Build the formula to insert in the cells
Range(lAddress).Formula = "=SUMIF(" & aAddress & "," & _
        aCell & "," & gAddress & ")"

From here, you can write a loop or some other incremental code to alter the row/column number variables as you see fit.