Need help with VBA sumif function

[brycew51]

Hello,

I am having difficulties getting the macro below to calculate a sumif (Import Commission Adj) and to sum two columns (Calculate Net). I am needing to sumif column F based on criteria in column G of the Macro2 tab into column I of the Macro6 tab for a variable amount of rows. Then sum columns H and I in column J in the Macro6 tab. When I run the current macro as is these two columns are blank. Code and visuals are below.

'Import Commission Adj


Range("I2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(Macro2!C[-2],Macro6!RC[-7],Macro2!C[-3])"
If Range("A3").Value <> 0 Then
Selection.AutoFill Destination:=Range("I2:I" & Range("A1000000").End(xlUp).Row)
End If
Range("I:I").Select
Selection.Copy
Range("I1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



'Calculate Net


Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
If Range("A3").Value <> 0 Then
Selection.AutoFill Destination:=Range("J2:J" & Range("A1000000").End(xlUp).Row)
End If
Range("J:J").Select
Selection.Copy
Range("J1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



Macro2 Tab - This is the data that I want to sumif in column I of the Macro6 tab. Sum the range in column F based on criteria range in column G.

Any help/tips would be appreciated! I'm fairly green with VBA and I'm trying to repair a macro written by someone else long ago.



Macro6 Tab - (Columns A-H are copy and pasted in a prior macro that is working properly). Criteria is in column B. Wanting to sumif data in Macro2 tab into column I of this tab. Then sum H and I in column J of this tab.

 

[sijpie]

The easiest to get the correct formula is to enter the formula by hand in I2 on Macro6, in your normal notation. So (not fully written out since I don't understand your criteria or sumrange)
=SUMIF(Macro2!G2,criteria, sum_range)

Once that formula is correct, then go to the menu File/Options/Formulas and in the second block tick R1C1 Reference.
Now go back to your cell I2. You will see the correct formula in the formula bar. Copy it and put it in your macro.

To get better replies to your questions:

1. Use VBA tags around your code (click the little VBA icon above the post area and paste your code in between the tags, see also the example here below in red)
2. Try to make your explanation as easy to read as possible. You are asking about a macro, and then in your example you mention two sheets with names Macro2 and Macro6. That is very confusing. Just give them a different name for your explanation, such as Sheet2 and Sheet6. Understand what i mean?
3. Always reread your post. Does it make sense to someone else?