Got the file!
So you want to disperse the data on 'Sales Log' to 'Orders by Status'
as follows:
Quoted:
PO#, Days since quoted, Total Amount
..where days since quoted will be Today-QuoteDate
Awarded:
PO#, Days since quoted, Total Amount
..where days since awarded will be Today-AwardDate
Delivered:
PO#, Total Amount
The following macro outputs to Sheets("Orders by Status") across 10
cols, with 1 blank col between the 3 sets of data. (Note that it sets
the headings and adjusts RowHeight of the headings row)
Sub SummarizeMasterList_v2()
Dim vDataIn, vDataOut(), n&, lRowC1&, lRowC2&, lRowC3&
Dim wksSource As Worksheet, wksTarget As Worksheet
Const lQDate& = 3 'col conating Quote Date
Const lADate& = 5 'col containing Award Date
Const lAmnt& = 6 'col containg Total Sales Amount
With ThisWorkbook
Set wksSource = .Sheets("Sales Log")
Set wksTarget = .Sheets("Orders by Status")
End With 'ThisWorkbook
With wksSource
vDataIn = .Range(.Cells(1), .Cells(1).End(xlDown)).Resize(, lAmnt)
End With 'wksSource
ReDim vDataOut(LBound(vDataIn) To UBound(vDataIn), 1 To 10)
'Initialize the first row with 'Status' headings
vDataOut(1, 1) = "Quoted": lRowC1 = 1
vDataOut(1, 2) = "Days Since Quoted"
vDataOut(1, 3) = "Total Amount"
vDataOut(1, 5) = "Awarded": lRowC2 = 1
vDataOut(1, 6) = "Days Since Quoted"
vDataOut(1, 7) = "Total Amount"
vDataOut(1, 9) = "Delivered": lRowC3 = 1
vDataOut(1, 10) = "Total Amount"
'Set the next available row in each column
For n = 2 To UBound(vDataIn) '//ignores list headings
Select Case vDataIn(n, 2)
Case vDataOut(1, 1)
lRowC1 = lRowC1 + 1
vDataOut(lRowC1, 1) = vDataIn(n, 1) 'PO#
vDataOut(lRowC1, 2) = Date - vDataIn(n, lQDate)
vDataOut(lRowC1, 3) = vDataIn(n, lAmnt)
Case vDataOut(1, 5)
lRowC2 = lRowC2 + 1
vDataOut(lRowC2, 5) = vDataIn(n, 1) 'PO#
vDataOut(lRowC2, 6) = Date - vDataIn(n, lADate)
vDataOut(lRowC2, 7) = vDataIn(n, lAmnt)
Case vDataOut(1, 9)
lRowC3 = lRowC3 + 1
vDataOut(lRowC3, 9) = vDataIn(n, 1) 'PO#
vDataOut(lRowC3, 10) = vDataIn(n, lAmnt)
End Select 'Case vDataIn(n, 2)
Next 'n
'Dump the data into the target sheet
With wksTarget.Cells(1)
.RowHeight = 24 '//edit to suit
.Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut
End With
'Cleanup
Set wksSource = Nothing: Set wksTarget = Nothing
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion