Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Countif - i,j,l counters probably mixed up
"Summary" Worksheet
A B C 1. Project Crit ProjCrit 2. Bravo 1 Bravo1 3. Bravo 1 Bravo1 4. Bravo 1 Bravo1 5. Bravo 2 Bravo2 6. Bravo 2 Bravo2 7. Bravo 3 Bravo3 8. Delta 2 Delta2 9. Delta 2 Delta2 wanted "PoStatus" Worksheet after following code execution to summarize Projects by Criteria count A B C D 1. Project Crit1 Crit2 Crit3 2. Bravo 3 2 1 3. Delta 2 Sub PoStatus() ActiveWorkbook.Sheets("Summary").Select Dim i As Integer: Dim j As Integer: Dim k As Integer Dim RngA As Range: Dim RngB As Range: Dim RngC As Range Columns("A:C").Select Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom i = 2: k = 2 Set RngA = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) Set RngB = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) Set RngC = Range(Cells(1, "C"), Cells(Rows.Count, "C").End(xlUp)) ' Calculate Sums for PO in Summary Do While i <= Range("A65000").End(xlUp).Row j = Application.CountIf(RngA, Cells(i, "A")) Worksheets("PoStatus").Cells(k, "A") = Cells(i, "A") l= Application.CountIf(RngC, Cells(i, "C")) Do While l <= j l= Application.CountIf(RngC, Cells(i, "C")) Select Case Cells(i,"B") Case "1" Worksheets("PoStatus").Cells(k, "B") = l Case "2" Worksheets("PoStatus").Cells(k, "C") = l Case "3" Worksheets("PoStatus").Cells(k, "D") = l End Select l = l +i : i = i + l ' suspected logic error there Loop k = k + 1 Loop ActiveWorkbook.Sheets("PoStatus").Select End Sub The desired worksheet result does properly populate. Help appreciated, J.P. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line chart, multiple series, data is inter-mixed | Charts and Charting in Excel | |||
Extracting mixed values in single column to multiple colums | Excel Programming | |||
Counters | Excel Programming | |||
Sum mixed colums in multiple worksheets | Excel Worksheet Functions | |||
counters | Excel Programming |