Need help with VBA sumif function

brycew51

New Member
Joined
Jan 27, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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.

brycew51_4-1643308909131.png



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.

brycew51_5-1643308952292.png
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?
 
Upvote 0

Forum statistics

Threads
1,203,069
Messages
6,053,359
Members
444,657
Latest member
jessejames1of3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top