Sumif formula in vba with offset function

[kanishkgarg]

Hi,

I need to enter a SUMIF function in my vba code and then paste it throughout the column. There are two worksheets - s1 and s2.

This is the formula I have come up with but it is not working:

 s2.Range("B3").Formula2R1C1 = "=SUMIF( " & s1.Name &!R3C5:R50000C5, s2.Name!R3C1 . Offset ( ,-1), s1.Name !R3C20:R50000C20 & ")"

Furthermore, I also need to paste it throughout the column till a specific point (which is determined by the number of cells with data in the adjacent column). -

'Selection.AutoFill Destination:=Range("B3:B" & Range("A" & Rows.Count).End(xlUp).Row)
'Range(Selection, Selection.End(xlDown)).Select

Any help would be greatly appreciated!

 

[gordsky]

Hi,

I need to enter a SUMIF function in my vba code and then paste it throughout the column. There are two worksheets - s1 and s2.

This is the formula I have come up with but it is not working:

 s2.Range("B3").Formula2R1C1 = "=SUMIF( " & s1.Name &!R3C5:R50000C5, s2.Name!R3C1 . Offset ( ,-1), s1.Name !R3C20:R50000C20 & ")"

Furthermore, I also need to paste it throughout the column till a specific point (which is determined by the number of cells with data in the adjacent column). -

'Selection.AutoFill Destination:=Range("B3:B" & Range("A" & Rows.Count).End(xlUp).Row)
'Range(Selection, Selection.End(xlDown)).Select

Any help would be greatly appreciated!

for the fill down you can just use

s1.range("B2:B" & range("A" & Rows.count).End(xlUp).Row).FillDown '<_ change s1 to s2 if needed

whats the range your first code refers to

 

[gordsky]

try this

s2.Range("B3").Formula2R1C1 = "=SUMIF(RC[1]:R[35]C[5],Sheet2!RC[1]:R[3]C[1].offset(0,-1),Sheet1!R[3]C[20]:R[50000]C[20])"

 

[kanishkgarg]

try this

s2.Range("B3").Formula2R1C1 = "=SUMIF(RC[1]:R[35]C[5],Sheet2!RC[1]:R[3]C[1].offset(0,-1),Sheet1!R[3]C[20]:R[50000]C[20])"

This is showing an error : Application-defined or object-defined error

 

[kanishkgarg]

for the fill down you can just use

s1.range("B2:B" & range("A" & Rows.count).End(xlUp).Row).FillDown '<_ change s1 to s2 if needed

whats the range your first code refers to

The range refers to the list of IDs from sheet 1 (s1) to identify the correct amounts.

 

[gordsky]

This is showing an error : Application-defined or object-defined error

I'm assuming it is showing an error as you didnt replace the "Sheet" reference with your s1 or s2...............It works fine for me
The code changed for you is as follows

s2.range("B3").FormulaR1C1 = "=SUMIF(" & s1.Name & "!R[3]C[5]:R[50000]C[5]," & s2.Name & "!R[3]C[0]," & s1.Name & "!R[3]C[20]:R[50000]C[20])"
s2.range("B3:B" & range("A" & Rows.count).End(xlUp).Row).FillDown '<--- change to s1 if this is where you want the formulas copied down

 

[kanishkgarg]

Thanks! It works.