PDA

View Full Version : Solved: Sumif/Sumproduct Across Columns & Rows



Xrull
07-03-2009, 09:57 AM
Hello,
I'm trying to write this formula as a VBA code:

=SUMIF(All!$P:$P,$C7&E$2&E$3,All!$I:$I)
Using VBA
Is there a way to replicate this code, so I don't have to do the
about 135 times?



With Sheets("Summary1")
With .Range...


Sub Macroz()
With Sheets("Summary1")
With .Range("B7", .Range("B" & Rows.Count).End(xlUp)).Offset(, 3)
.Formula = "=SUMIF(All!$P:$P,$C7&E$2&E$3,All!$I:$I)"
.Value = .Value
With Sheets("Summary1")
With .Range("B7", .Range("B" & Rows.Count).End(xlUp)).Offset(, 4)
.Formula = "=SUMIF(All!$P:$P,$C7&F$2&F$3,All!$I:$I)"
.Value = .Value
End With
With Sheets("Summary1")
With .Range("B7", .Range("B" & Rows.Count).End(xlUp)).Offset(, 5)
.Formula = "=SUMIF(All!$P:$P,$C7&G$2&G$3,All!$I:$I)"
.Value = .Value
End With
End With
End With
End With
End With
End Sub

This code works within the range E7:EI71

I crossed posted two threads here:

http://www.mrexcel.com/forum/showthread.php?t=400260&highlight=xrull
http://www.mrexcel.com/forum/showthread.php?t=400358&highlight=xrull

Bob Phillips
07-03-2009, 10:26 AM
Completely untested


Sub Macroz()
With Sheets("Summary1")
With .Range("E7", .Range("B" & Rows.Count).End(xlUp)).Offset(, 3).Resize(, 3)
.Formula = "=SUMIF(All!$P:$P,$C7&E$2&E$3,All!$I:$I)"
.Value = .Value
End With
End With
End Sub

p45cal
07-03-2009, 10:33 AM
XLD just beat me to it but has been tested. Use the R1C1 notation where all the formulae are exactly the same:
Sub blah()
With ThisWorkbook.Sheets("Summary").Range("$E$7:$G$11")
.FormulaR1C1 = "=SUMIF(All!C16,RC3&R2C&R3C,All!C9)"
.Value = .Value
End With
End Subor since I can't see anything in column B I cobbled together something else:Sub blah2()
With ThisWorkbook.Sheets("Summary")
With Range(.Range("C6").Offset(1), .Range("C" & .Rows.Count).End(xlUp)).Offset(, 2).Resize(, 3)
.FormulaR1C1 = "=SUMIF(All!C16,RC3&R2C&R3C,All!C9)"
.Value = .Value
End With
End With
End Sub

Xrull
07-03-2009, 10:39 AM
EL XID,
The code works well for the first 3 columns, but when I modify it for to offset for 137, it fails,

Sub Macroz1()
With Sheets("Summary1")
With .Range("E7", .Range("B" & Rows.Count).End(xlUp)).Offset(, 137).Resize(, 137)
.Formula = "=SUMIF(All!$P:$P,$C7&E$2&E$3,All!$I:$I)"
.Value = .Value
End With
End With
End Sub

What caused it to fail?

Thanks,
Xrull

Bob Phillips
07-03-2009, 10:40 AM
Because it should be Offset(,3) still, not 137. Only the Resize should be 137.

Xrull
07-03-2009, 10:48 AM
P45Cal,
How do I modify your code to go from E7:EI71
Thanks,
Xrull

Xrull
07-03-2009, 10:53 AM
P45Cal,
I did this:

Sub blah2()
With ThisWorkbook.Sheets("Summary1")
With Range(.Range("C6").Offset(1), .Range("C" & .Rows.Count).End(xlUp)).Offset(, 2).Resize(, 137)
.FormulaR1C1 = "=SUMIF(All!C16,RC3&R2C&R3C,All!C9)"
.Value = .Value
End With
End With
End Sub

and it worked.
Thanks,
Xrull

p45cal
07-03-2009, 11:25 AM
If this is solved, could you mark it as such.
Also, it would be netiquette to tell those at MrExcel that the problem is solved. It would save someone working unnecessarily on it.
(They probably would like to have known that you'd posted here too.)