# SUMIF formula to VBA sintax problem

#### didijaba

##### Well-known Member
HI, I can't get this formula right, pls help.
Code:
``````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``````

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
What would the formula look like if written in a cell ???

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.

Easier to use:
Code:
``````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:
Code:
``````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``````

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 .

Replies
8
Views
472
Replies
2
Views
200
Replies
2
Views
368
Replies
0
Views
355
Replies
12
Views
686

1,196,366
Messages
6,014,843
Members
441,850
Latest member
peh16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back