The following Code works perfectly to read Invoices in Sheet2 for
Selected Customers in Sheet1
and write sum for those selected Customers inSheet3
'
Sub FilterandSumbyCustomer
'declarations
Dim bnDup As Boolean
Dim iCounter As Integer, iNumCust As Integer
Dim strCust() As String, strCustActive As String
Dim varVal As Variant, varValSum() As Variant
'
'First, get list of unique customers from Sheet1
Sheet1.Activate: Cells(1, 1).Select
Do Until ActiveCell = ""
bnDup = False
For iCounter = 1 To iNumCust
If strCust(iCounter) = ActiveCell Then
bnDup = True
End If
Next iCounter
iNumCust = iNumCust + 1
ReDim Preserve strCust(iNumCust)
strCust(iNumCust) = ActiveCell
ActiveCell.Offset(1, 0).Select
Loop
'
'Second, get required data from sheet2 (invoice Amount and YearMonth)
Sheet2.Activate: Cells(1, 1).Select: ReDim varValSum(iNumCust)
Do Until ActiveCell = ""
strCustomersActive = ActiveCell: varVal = ActiveCell.Offset(0,
1).Value
For iCounter = 1 To iNumCust
If strCustomersActive = strCust(iCounter) Then
varValSum(iCounter) = varValSum(iCounter) + varVal
End If
Next iCounter
ActiveCell.Offset(1, 0).Select
Loop
'
'Third to Sheet 3 and print
Sheet3.Activate: Cells(1.1).Select
For iCounter = 1 To iNumCust
ActiveCell = strCust(iCounter): ActiveCell.Offset(0, 1) =
varValSum(iCounter)
ActiveCell.Offset(1, 0).Select
Next iCounter
End Sub
.................................................. .................................................. ....................................
I need to extend this code to obtain in Sheet3 a history distribution
in Columns by YearMonth.
I am aware I could have achieved this easily with a pivot table, but I
want to master logic & syntax with VBA.
'
Sheet1
Customer
A
L
B
'
Sheet2
Customer Invoice YM
A 200 0804
B 600 0802
B 400 0802
K 1000 0712
K 300 0804
L 100 0801
'Sheet3
Customer 0712 0801 0802 0803 0804
A 200
B 1000
L 100
.................................................. .................................................
Before the Second Section, I need to generate the Calendar from the
Min & Max in Sheet2
which are 0712 and 0804.
Aside from the additional declarations, How do I loop to generate this
Calendar ?
Within the Second and Third Section I have to handle the 2nd Dimension
of the array to post the amount for the month.
I was considering using a Select Case but there is probably a better
way thru the array index.
Can you help me there ?
Thank you for your help.
J.P.