PDA

View Full Version : Need to replace SUMIFS function with VBA code



lakamas
11-12-2015, 06:21 AM
I need to replace this function:

=SUMIFS($D:$D;$A:$A;$G2;$B:$B;$H2;$C:$C;$N$1)

to VBA code and find a way to loop it so I can fill column K (based on the length of column G) of the example file with the correct sums.

14743

File details:
Columns A,B,C,D are data
Columns G,H are the criteria I need to use
Column K is where I need to fill the summed amounts from Column D based on the corresponding criteria from G and H columns and N1 value

p45cal
11-12-2015, 07:22 AM
with the active sheet being the relevant sheet:
Sub blah()
With Range("K2:K22")
.FormulaR1C1 = "=SUMIFS(C4,C1,RC7,C2,RC8,C3,R1C14)"
.Value = .Value
End With
End Sub
or a bit more flexibly:
Sub blah2()
With Range("G1").CurrentRegion
With .Resize(.Rows.Count - 1, 1).Offset(1, 4)
.FormulaR1C1 = "=SUMIFS(C4,C1,RC7,C2,RC8,C3,R1C14)"
.Value = .Value
End With
End With
End Sub

lakamas
11-19-2015, 02:38 PM
Thank you , your code helped me solve my problems :)