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.

jordanz · Accepted Answer · 2022-12-14 13:41:07Z

4

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.

edited Dec 14, 2022 at 13:41

jordanz

3734 silver badges13 bronze badges

answered Nov 18, 2013 at 16:59

Jaycal

2,0871 gold badge13 silver badges21 bronze badges

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

3 Comments

Amatya Over a year ago

This is great! Thanks a lot. I have one equestion: How you you write Range(M2:M17)=L2/G2 ? I tried replacing the range and the cells with expressions that look like yours but I am just mired in errors.

Amatya Over a year ago

Ok I got it. Range(lAddress).Formula="=" & aCell & "/" & bCell One of the things I was trying was Range(lAddress).Formula=""=" & aCell & "/" & bCell " I guess I don't need the outside quotes because when I start with "="& the program understands that it's gonna make a string out of it all. This is still confusing. Thanks

Jaycal Over a year ago

variables (i.e. aCell, bCell) do not need to be surrounded with quotes. Given your example, it should be written as Range(lAddress).Formula = "=" & aCell & "/" & bCell

Collectives™ on Stack Overflow

Excel VBA formula with variables

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related