Using variables in SumIf

I am trying to put a SUMIF statementin VBA into a moving range of cells as the program cycles through. Therefor I need the criteria of the SUMIF formula to change as well. I want to put variables in for the range in the formula so as the variable lastRow increments, the range the formula searches also increments with that. Any suggestions?

ie

cells(lastRow,4).formula = "=sumif( b2:" & variable referencing another cell & "," & variable referencing text of a cell & ", c3:" & variable referencing another cell & ")"

Try making all the inner quotes equal double quotes like:

Cells(lastRow, 4).Formula = "=sumif( b2:"" & variable referencing another cell & "","" & variable referencing text of a cell & "", c3:"" & variable referencing another cell & "")"

hth

Here is the piece that has the sum if and the variable used. The rest of the macro is long and proprietary so I can't post it.
I am going by the logic of the sumif formula of sumif(range to be searched, seach for this, sum this range)

Dim rownum1 as variant, dim rownum2 as variant, dim rownum3 as variant
Dim openrow as integer
dim lastrow as integer
rownum1= cells(lastrow,2)
rownum2=cells(openrow,2)
rownum3=cells(lastrow,3)

cells(openrow,4).formula = "=sumif(b2:" & rownum1 & "," & rownum2 & ",c3:" & rownum3 &")"

This way as the variables for lastrow and openrow increase, the formula will move down the sheet and the criteria range will expand.

Try:

rownum1= cells(lastrow,2).value
rownum2=cells(openrow,2).value
rownum3=cells(lastrow,3).value

Does:

Cells(openrow, 4).Formula = "=sumif(b2:"" & rownum1 & "","" & rownum2 & "",c3:"" & rownum3 & "")"

work?

ngoellner,

Please use code tags around your code snippets... I have corrected them so far.

Maybe this at a guess

Sub test()
    Dim StartRow As Long, LastRow As Integer

    StartRow = 2
    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    Cells(StartRow, 4).Formula = "=sumif(B2:" & LastRow & "," & Cells(StartRow, 2) & ",C3:" & LastRow & ")"

End Sub

I figured out the problem. When setting the variables

numrow1 = cells(last,2)
numrow2=cells(openrow,3)
etc.

It needed the .address extention

numrow1 = cells(last,2).address

It was picking up the value of the cells and making an incorrect range rather than getting the location of that cell to complete the second half of the range.

Thanks for the help and the quick responses