Sumif Range to new Worksheet
 

I am trying to summarize Purchase Orders by Project and Vendor
from POSummary into VendorSummary
..
Source WorkSheet POSummary
A B C D
Project PO# Vendor PO Value
693 56 Alpha 1,000
693 50 Alpha 2,000
693 54 Bravo 1,000
231 13 Charlie 4,000
231 33 Charlie 2,000
231 23 Bravo 3,000
231 49 Alpha 3,000
231 12 Alpha 5,000
.....
Expected result inDestination VendorSummary
A B C
Project Vendor PO Value
693 Alpha 3,000
693 Bravo 1,000
231 Charlie 6,000
231 Bravo 3,000
231 Alpha 8,000

Somehow, I must have my i's & j's indexes crossed
but I canot find my error.
Help appreciated.
J.P.

Sub Vendor()

Dim i As Integer ' Source Worksheet Current Row Counter
Dim j As Integer ' Criteria Range Counter
Dim k As Integer ' Destination Worksheet Current Row Counter
Dim RngC As Range ' Vendor Range
Dim RngD As Range ' PO Value Range
'
'Sort Source WorkSheet by Project & Vendor
Columns("A:D").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("C2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
'
i = 2: k = 2 ' Data start in Row2
Set RngC = Range(Cells(1, "C"), Cells(Rows.Count, "C").End(xlUp))
Set RngD = Range(Cells(1, "D"), Cells(Rows.Count, "D").End(xlUp))

' Calculate Sums by Vendor in VendorSummary
Do While i <= Range("A65000").End(xlUp).Row
j = Application.CountIf(RngC, Cells(i, "C"))
Worksheets("VendorSummary").Cells(k, "A") = Cells(i, "A") '
Project Code
Worksheets("VendorSummary").Cells(k, "B") = Cells(i, "C") ' Vendor
Name
Worksheets("VendorSummary").Cells(k, "C") =
Application.SumIf(RngD, Cells(i, "C"), RngD)
k = k + 1: i = i + j
Loop

End Sub

Sumif Range to new Worksheet
 

Why not use a pivot table. It requires no code and it will give you exactly
the results you are looking for with a lot less effort...
--
HTH...

Jim Thomlinson


"u473" wrote:

I am trying to summarize Purchase Orders by Project and Vendor
from POSummary into VendorSummary
..
Source WorkSheet POSummary
A B C D
Project PO# Vendor PO Value
693 56 Alpha 1,000
693 50 Alpha 2,000
693 54 Bravo 1,000
231 13 Charlie 4,000
231 33 Charlie 2,000
231 23 Bravo 3,000
231 49 Alpha 3,000
231 12 Alpha 5,000
.....
Expected result inDestination VendorSummary
A B C
Project Vendor PO Value
693 Alpha 3,000
693 Bravo 1,000
231 Charlie 6,000
231 Bravo 3,000
231 Alpha 8,000

Somehow, I must have my i's & j's indexes crossed
but I canot find my error.
Help appreciated.
J.P.

Sub Vendor()

Dim i As Integer ' Source Worksheet Current Row Counter
Dim j As Integer ' Criteria Range Counter
Dim k As Integer ' Destination Worksheet Current Row Counter
Dim RngC As Range ' Vendor Range
Dim RngD As Range ' PO Value Range
'
'Sort Source WorkSheet by Project & Vendor
Columns("A:D").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("C2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
'
i = 2: k = 2 ' Data start in Row2
Set RngC = Range(Cells(1, "C"), Cells(Rows.Count, "C").End(xlUp))
Set RngD = Range(Cells(1, "D"), Cells(Rows.Count, "D").End(xlUp))

' Calculate Sums by Vendor in VendorSummary
Do While i <= Range("A65000").End(xlUp).Row
j = Application.CountIf(RngC, Cells(i, "C"))
Worksheets("VendorSummary").Cells(k, "A") = Cells(i, "A") '
Project Code
Worksheets("VendorSummary").Cells(k, "B") = Cells(i, "C") ' Vendor
Name
Worksheets("VendorSummary").Cells(k, "C") =
Application.SumIf(RngD, Cells(i, "C"), RngD)
k = k + 1: i = i + j
Loop

End Sub

Sumif Range to new Worksheet
 

Thank you for the advice, but since it does not involve too many lines
of
codes I wanted to grasp it in VBA first.
But you brought a good point. How do I generate the same result with
a Pivot Table through VBA ?

Sumif Range to new Worksheet
 

Trying to generate a pivot table with the books examples I already
have was an absolute headache.
What I am trying to achieve now I could have done it much faster with
Access but I need to master Excel VBA.
I need to automate my operations and I will have to return to Pivot
Tables with VBA later on because it offers potentials.
..
Meanwhile, returning to my original code, I identified my problem to
have a summary by Project and Vendor.
..
On the first pass I should have the following values :
Project Vendor PO Value i j k
231 Alpha 8000 2 2 2
..
But in fact j = 4 instead of 2, because it counts all the Vendors of
that name for All projects
The solution I could come up with is to concatenate Project and PO#
and later do a Sumif on that.
'Is it the proper solution ? or am I confused with my syntax ?
Thank you for help
J.P.