SUMIF formula to VBA sintax problem

[didijaba]

HI, I can't get this formula right, pls help.

Sub test()
Dim i As Long
Dim lastrow As Long
lastrow = 20
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheet3.Range("B2:B" & lastrow)
Set rng3 = Sheet3.Range("Q2:Q" & lastrow)
Set rng2 = Sheet3.Range("J1")
Sheet3.Cells(lastrow + 1, 17).Formula = "=SUMIF("" & rng1 & "",""" & rng2 & ""","""" & rng3 & """")"""""""

End Sub

 

[Michael M]

What would the formula look like if written in a cell ???

 

[didijaba]

It would look something like =SUMIF(Data!B2:B20; Data!J1; Data!Q2:Q20). But range is dynamic, it needs to adjust, that is reason I entered lastrow.

 

[RoryA]

Easier to use:

Sub test()
Dim lastrow As Long
lastrow = 20

Sheet3.Cells(lastrow + 1, 17).Formula = "=SUMIF(B2:B" & lastrow & ",J1,Q2:Q" & Lastrow & ")"

End Sub

or based on what you seem to be doing:

Sub test()
Dim lastrow As Long
lastrow = 20

Sheet3.Cells(lastrow + 1, 17).FormulaR1C1 = "=SUMIF(R2C2:R[-1]C2,R1C10,R2C:R[-1]C)"

End Sub

 

[didijaba]

Thanks guys, I did this last night so my head was not working as it should have . I took Michael M advice and run it as formula in sheet, then VBA is no problem. Thanks Michael and thanks RoryA .