VBA Sumif Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-3074298%22%20slang%3D%22en-US%22%3EVBA%20Sumif%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074298%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%2Ftips%20would%20be%20appreciated!%20I'm%20fairly%20green%20with%20VBA%20and%20I'm%20trying%20to%20repair%20a%20macro%20written%20by%20someone%20else%20long%20ago.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20difficulties%20getting%20the%20macro%20below%20to%20calculate%20a%20sumif%20(Import%20Commission%20Adj)%20and%20to%20sum%20two%20columns%20(Calculate%20Net).%20I%20am%20needing%20to%20sumif%20column%20F%20based%20on%20criteria%20in%20column%20G%20of%20the%20Macro2%20tab%20into%20column%20I%20of%20the%20Macro6%20tab%20for%20a%20variable%20amount%20of%20rows.%20Then%20sum%20columns%20H%20and%20I%20in%20column%20J%20in%20the%20Macro6%20tab.%20When%20I%20run%20the%20current%20macro%20as%20is%20these%20two%20columns%20are%20blank.%20Code%20and%20visuals%20are%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Import%20Commission%20Adj%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERange(%22I2%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22%3DSUMIF(Macro2!C%5B-2%5D%2CMacro6!RC%5B-7%5D%2CMacro2!C%5B-3%5D)%22%3CBR%20%2F%3EIf%20Range(%22A3%22).Value%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22I2%3AI%22%20%26amp%3B%20Range(%22A1000000%22).End(xlUp).Row)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ERange(%22I%3AI%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22I1%22).PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'Calculate%20Net%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERange(%22J2%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22%3DRC%5B-2%5D%2BRC%5B-1%5D%22%3CBR%20%2F%3EIf%20Range(%22A3%22).Value%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3ESelection.AutoFill%20Destination%3A%3DRange(%22J2%3AJ%22%20%26amp%3B%20Range(%22A1000000%22).End(xlUp).Row)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ERange(%22J%3AJ%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22J1%22).PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMacro2%20Tab%20-%20This%20is%20the%20data%20that%20I%20want%20to%20sumif%20in%20column%20I%20of%20the%20Macro6%20tab.%20Sum%20the%20range%20in%20column%20F%20based%20on%20criteria%20range%20in%20column%20G.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22brycew51_4-1643308909131.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343038iA5EA5D94C1ECB35E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22brycew51_4-1643308909131.png%22%20alt%3D%22brycew51_4-1643308909131.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMacro6%20Tab%20-%20(Columns%20A-H%20are%20copy%20and%20pasted%20in%20a%20prior%20macro%20that%20is%20working%20properly).%20Criteria%20is%20in%20column%20B.%20Wanting%20to%20sumif%20data%20in%20Macro2%20tab%20into%20column%20I%20of%20this%20tab.%20Then%20sum%20H%20and%20I%20in%20column%20J%20of%20this%20tab.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22brycew51_5-1643308952292.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343039iFBBD6F578E9057CF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22brycew51_5-1643308952292.png%22%20alt%3D%22brycew51_5-1643308952292.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074298%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

Hello,

 

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.

 

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

 

 

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

 

 

 

 

0 Replies