Help to improve sum/countifs code function 2

[Rzrbkpk]

I'm soliciting this community's help for suggestions on making my code better. My current code does function properly, but I'd like to make it more efficient and not so large. The code listed below has been edited to only include a sampling of the case statement. The total code has 20 cases and is repeated for multiple worksheets.

The code's purpose is to compile metrics for a source of data (worksheet "Raw_Data") and post those metrics in a template (worksheet "Cover Page(Asia)").

Sub Populate_OrderTables()
    Dim b, c, i, j, k, l, m, n, o, p, q As Integer
    Dim ordstatCol, ordtypeCol, subbuCol As Range
    Dim dataWS_RD As Worksheet
    Dim rowLimit As Integer
    Dim colLimit As Integer
    Dim xlapp As Excel.Application
    Dim xlWb As Workbook
    Dim xlWs As Worksheet


Set dataWS_RD = ThisWorkbook.Sheets("Raw_Data")

Worksheets("Raw_Data").Activate

rowLimit = dataWS_RD.UsedRange.Rows.Count
colLimit = dataWS_RD.UsedRange.Columns.Count

'Find Order Status column
    For i = 1 To colLimit
        If Left(dataWS_RD.Cells(1, i).Value, 12) = "Order Status" Then
            Set ordstatCol = Range(Cells(1, i), Cells(rowLimit, i))
            Exit For
        End If
    Next i

'Find Order Type column
    For p = 1 To colLimit
        If Left(dataWS_RD.Cells(1, p).Value, 10) = "Order Type" Then
            Set ordtypeCol = Range(Cells(1, p), Cells(rowLimit, p))
            Exit For
        End If
    Next p
    
'Find Sub Business Unit column
    For q = 1 To colLimit
        If Left(dataWS_RD.Cells(1, q).Value, 17) = "Sub Business Unit" Then
            Set subbuCol = Range(Cells(1, q), Cells(rowLimit, q))
            Exit For
        End If
    Next q


Worksheets("Cover Page(Asia)").Activate

For Each y In ActiveSheet.Range("b4", "b7:b33").Cells
    Select Case y
    Case "Level 1"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 1")))
    Case "Level 2"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 2")))
    Case "Level 3"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 3")))
    Case "Level 4"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 4")))
    Case "Level 5"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 5")))
    Case "Level 6"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 6")))
    Case "Level 7"
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7"), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level 7")))
    End Select
End Sub

 

[PWD]

Hi. As this community knows I'm no great expert but the very first thing I've noticed is your Dim statements don't appear to be doing what you think they are. As far as I know you still have to declare the variable type each time even though they are all on the same line as "Dim"

Dim b As Integer, c As Integer

It's quitting time here now so I'll be back.

Many thanks,
D€$

 

[SkipVought]

Hi,

I'm assuming (based on one of your previous posts) that you may be the kind of person that looks at a problem, and with limited knowledge of Excel, decides that THIS is the solution to your problem.

Suppose you describe the functional problem, that is, WHAT it is that you wish to accomplish, rather than HOW you think that you can best solve this problem. I fear that you are overlooking a simpler unknown solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I would answer Skip's question, but I would also look at this:

Dim x As Integer

For Each y In ActiveSheet.Range("b4", "b7:b33").Cells
    x = Val(Split(y, " ") (1)) [green]'Pick the number from "Level X"[/green]
    y.Offset(0, 1).Value = WorksheetFunction.Sum _
        (WorksheetFunction.CountIfs(subbuCol, "Asia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "eCommerce" & char(10) & "China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Bangladesh" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Cambodia" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - China" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - India" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Pakistan" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Thailand" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)), _
        (WorksheetFunction.CountIfs(subbuCol, "*" & "GS - Vietnam" & "*", ordstatCol, "<>Closed", ordtypeCol, "Level " & x)))
End Sub

Have fun.

---- Andy

 

[Rzrbkpk]

Thanks for your assistance Andy. Your post is helpful and very beneficial. People like you help make this community of users better.

 

[Andrzejek]

You are welcome, but please address Skip’s post.
You may like his response even better, who knows...


Have fun.

---- Andy

 

[Gavona]

Did you see this at the bottom of Andrezejek's post?
*Like this post?
Star it!

From your reply it seems like you should click that link!

Gavin

 

[SkipVought]

Looks as if your version of Excel is 2007+. You could save yourself the "'find XXXXX column" loops to assign the ranges for those columns (saving some 20 loc) by making your table a Structured Table.

Your for...next loop does not do what I believe that you want it to do, that is b4, b7, b8, b9 etc.

For Each y In ActiveSheet.Range("b4", "b7:b33").Cells

Rather it references B4:B33. Check it out

Sub testit()
    Dim r As Range
    For Each r In ActiveSheet.Range("b4", "b7:b33").Cells
        r.Value = r.Row
    Next
End Sub

Now it might not materially affect your sheet, because of the data that you actually have in B5:B6, but be aware that the code does not behave as it seems that you might have intended.

Going back to my earlier comment regarding Structured Table, this entire thing might all be able to be done on your sheet without VBA, using the SUMPRODUCT() function to count coincidental data in multiple columns and it might look something like this, assuming that your Structured Table Name is tAsia, and AA1 and following contains names like Asia, eCommerce...
[tt]
=SUMPRODUCT((tAsia[Sub Business Unit]=AA$1)*(tAsia[Order Status]<>"Closed")*(tAsia[Order Type]=$A4))
[/tt]
Just some observations.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

Since your For/Next loops are looping through the same range, is there a way of combining them? It looks like you're searching for the 1st instance of your criteria since you have the Exit For within all of the loops. I would think you'd want the values of ordstatCol, ordtypeCol and subbuCol be based on the same dataset. If these items are all on the same row of data, you could reduce your code to the following:

For i = 1 To colLimit
    If Left(dataWS_RD.Cells(1, i).Value, 12) = "Order Status" And _
       Left(dataWS_RD.Cells(1, i).Value, 10) = "Order Type" And _
       Left(dataWS_RD.Cells(1, i).Value, 17) = "Sub Business Unit" Then
            Set ordstatCol = Range(Cells(1, i), Cells(rowLimit, i))
            Set ordtypeCol = Range(Cells(1, i), Cells(rowLimit, i))
            Set subbuCol = Range(Cells(1, i), Cells(rowLimit, i))
            Exit For
    End If
Next i

or is they are different....

For p = 1 To 3
    q = Choose(p, 2, 0, 7)
    Value$ = Choose(p, "Order Status", "Order Type", "Sub Business Unit")
    For i = 1 To colLimit
        If Left(dataWS_RD.Cells(1, i).Value, 10 + q) = Value$ Then
           Select Case q
           Case 1
                Set ordstatCol = Range(Cells(1, i), Cells(rowLimit, i))
           Case 2
                Set ordtypeCol = Range(Cells(1, i), Cells(rowLimit, i))
           Case 3
                Set subbuCol = Range(Cells(1, i), Cells(rowLimit, i))
           End Select
           Exit For
        End If
    Next i
Next p

 

[SkipVought]

My contention is that the OP may not need any VBA code at all!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

Gavona,
Thanks for the reminder. I don't usually forget.

Skip,
You may have assumed incorrectly. I don't believe this is the solution, which is why I posted. My solution works, but I believe there is a better solution. I've already admitted that I tend to over-complicate at times when there is a simpler solution. On a side note, it sounds condescending and insulting when you indicate someone has limited knowledge of a subject. A comment like that is really unnecessary and has zero benefit to the post, but that's neither here nor there, so I'll move on.

I have a propensity for either over-explaining or under-explaining given my audience, so the following may not contain all the details you're asking for. Without posting the spreadsheet due to the confidential nature of the data, here is a sample of what I'm looking at.

Business Unit    Sub Business Unit    Order Type   FY Year   [etc.]
U.S.             New York             Level 1      2014
Int'l            Asia - China         Level 1      2014
Int'l            Asia - India         Level 1      2014
Int'l            GS - Pakistan        Level 3      2014
U.S.             Georgia              Level 1      2014
Int'l            Europe - France      Level 1      2014

The result I'm looking for is how many of each order type does a grouped Sub Business Unit have. The grouping that I used in my original post contains:

[Asia Group]
Asia - China
Asia - India
Asia - Japan
GS - Bangladesh
GS - Cambodia
GS - China
GS - India
GS - Pakistan
GS - Thailand
GS - Vietnam

Given this grouping and the sample data provided above. Here are the results I need/expect:

Asia Group
Level 1: 2
Level 3: 1

 

[SkipVought]

1) you set up a table to group your business units

[b]Table Name: tGroups
Group         GroupName[/b]
Asia Group    Asia - China
Asia Group    Asia - India
Asia Group    Asia - Japan
Asia Group    GS - Bangladesh
Asia Group    GS - Cambodia
Asia Group    GS - China
Asia Group    GS - India
Asia Group    GS - Pakistan
Asia Group    GS - Thailand
Asia Group    GS - Vietnam
US Group      New York
US Group      Georgia
Europe Group  Europe - France

2) you add a Group column to your main table that I named tMain

[b]Table Name : tMain
Business Unit  Sub Business Unit  Order Type  FY Year  Group[/b]
U.S.           New York           Level 1     2014     US Group
Int'l          Asia - China       Level 1     2014     Asia Group
Int'l          Asia - India       Level 1     2014     Asia Group
Int'l          GS - Pakistan      Level 3     2014     Asia Group
U.S.           Georgia            Level 1     2014     US Group
Int'l          Europe - France    Level 1     2014     Europe Group

3) your results in row 14 column C on my sheet

[b]
  A        B          C[/b]
Level 1  Asia Group   2  [b]=SUMPRODUCT((tMain[Order Type]=A14)*(tMain[Group]=B14))[/b]
Level 3  Asia Group   1

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

Thanks Skip. That is a great solution. The table you have labeled tMain is a data set that is repopulated each week. Considering that, I would need to create and populate the Group column each time and I assume re-create the sumproduct formula since the cell reference would convert to N/A once the original data was gone. Is that accurate?

 

[SkipVought]

1) how do you repopulate your table?

2) once populated, a simple query can regenerate the Groups table with a Refresh.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, Item 2) above would not be correct!

I would need to create and populate the Group column each time and I assume re-create the sumproduct formula since the cell reference would convert to N/A once the original data was gone.

Depending on how your Main table is repopulated, the Group column MAY need to be added at worse and simply adjusted for total rows a best.

The only time that you might get a #N/A!, would be when a NEW Sub Business Unit value might appear in Main that is not currently in your Groups table.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rzrbkpk]

The table is repopulated by copy/paste. The origin data is an output from a program that creates a .csv file in a year\month\week directory structure.

 

[SkipVought]

If you were to IMPORT the .csv via Data > Get external Data > Text data... 1) you could assure that each column would be assigned to TEXT, DATE, GENERAL in the parsing and 2, the refresh would be simply a "Refresh"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]