Solved: Sumif/Sumproduct Across Columns & Rows

[Xrull]

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]

Completely untested

[vba]
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
[/vba]

[p45cal]

XLD just beat me to it but has been tested. Use the R1C1 notation where all the formulae are exactly the same:
[vba]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 Sub[/vba]or since I can't see anything in column B I cobbled together something else:[vba]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
[/vba]

[Xrull]

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]

Because it should be Offset(,3) still, not 137. Only the Resize should be 137.

[Xrull]

P45Cal,
How do I modify your code to go from E7:EI71
Thanks,
Xrull

[Xrull]

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]

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.)