Formula to the last row

[th259]

Range("G3").FormulaR1C1 = "=SUMIF(R[13]C[1]:R[42]C[1],RC[1],R[13]C:R[42]C)"

I had the code above, but I want to change it so that it goes to the last row in the worksheet.

I changed it to:

 Dim lastRow As Long
        lastRow = Cells(Rows.Count, "A").End(xlUp).Row
        ' Adjust the SUMIF formula ranges to absolute references
        Range("G3").FormulaR1C1 = "=SUMIF(R16C17:R" & lastRow & "C17,RC[1],R16C16:R" & lastRow & "C16)"

but the final formula after the VBA is

=SUMIF($C$16:$C$26,Q3,$B$16:$B$26)

instead of the formula I wanted

=SUMIF($Q$15:$Q$26,Q3,$P$15:$P$26)

Can somebody please tell me how to fix this?

 

[Phuoc]

Try change R16 to R15:

 Range("G3").FormulaR1C1 = "=SUMIF(R15C17:R" & lastRow & "C17,RC[1],R15C16:R" & lastRow & "C16)"

 

[th259]

I'm sorry, I made a mistake copying and pasting. The row the VBA picked is fine, it's the column. The code is picking up column C instead of Q and column B instead of P.

 

[MARK858]

It isn't picking up the wrong columns for me

Book1
	A	B	C	D	E	F	G
1	Order ID
2	106-2-F90
3	104-6-N38						0
4	109-2-N19
5	102-8-N50
6	108-3-F66
7	103-11-F27
8	103-26-F95
9	105-25-N18
10	104-7-N07
11	103-15-F55
12	106-6-N94
13	104-17-N48
14	105-28-N51
15	104-31-N32
16	105-16-N08
17	102-27-F90
18	110-5-N72
19	108-1-F09
20	107-14-N42
21	105-22-N14
Sheet1

Cell Formulas
Range	Formula
G3	G3	=SUMIF($Q$16:$Q$21,H3,$P$16:$P$21)

 

[th259]

The H3 was supposed to be Q3. I figured it out, there are some cutting and pasting columns afterward, so it was supposed to be column 8. I fixed it and it worked.

Range("G3").FormulaR1C1 = "=SUMIF(R16C8:R" & lastRow & "C8, RC[1], R16C7:R" & lastRow & "C7)"

Thank you for checking!