?
Solved

Add Formulas in VBA to Sub-Total Rows

Posted on 2013-01-16
4
Medium Priority
?
645 Views
Last Modified: 2013-01-17
I have a sheet which I can sort and subtotal fine in VBA. Here is the code:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Range("A1").Select
    ActiveWorkbook.Worksheets("December 12").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("December 12").Sort.SortFields.Add Key:=Range( _
        "D2:D55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("December 12").Sort
        .SetRange Range("A1:H55")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

The code above puts the count of the number of rows in the sub-total section in column D of the sub-total rows in the sheet for each subtotal section. This is what I want it to do. Now, on each sub-total row, there are three other columns that I want the code to calculate a number and place it at the foot of the sub-total columns just as it does for the one sub-total the code is already calculating.

Now that we have sub-total rows in the worksheet, I need to add to the code so that VBA will look for each sub-total row and run a COUNTIF formula on columns E, F, and H. For columns E and F, it is to COUNTIF the criteria is "Y", and for column H, the criteria is "N". Then it needs to put the result of the COUNTIF formulas on the sub-total row for each section of sub-totals. The number of rows in any given sub-total section is dynamic and can change any time the code is run. I just do not know how to have VBA locate only the rows in each sub-total section, find the sub-total rows, and run the COUNTIF formula on the proper column with the proper criteria.

After it completes the COUNTIF process, I need VBA to set each sub-total row, columns A through H, to be highlighted in yellow and boldfaced.

A copy of how the sheet is to look is attached.

Any help you can provide on this will be appreciated!
Sample-Account-Spreadsheet.xlsx
0
Comment
Question by:Glenn Stearns
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 38786431
Hi here it is

You have 2 options you can run the routine created sepeartly after having produced your subtotal (or ran your vba subtotal) or you can add this line
InsertCOUNTIF
In your vba code to be like this

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Range("A1").Select
    ActiveWorkbook.Worksheets("December 12").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("December 12").Sort.SortFields.Add Key:=Range( _
        "D2:D55"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("December 12").Sort
        .SetRange Range("A1:H55")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    InsertCOUNTIF
End Sub



Sub InsertCOUNTIF()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long
Dim sRange As String, sFormula As String
Dim lcountif As Long

Set WS = ActiveSheet
MaxRow = WS.Range("C" & WS.Rows.Count).End(xlUp).Row

For I = 2 To MaxRow
    If Left(LCase(WS.Cells(I, "D").Formula), 9) = "=subtotal" Then
        sFormula = WS.Cells(I, "D").Formula
        sRange = Right(sFormula, Len(sFormula) - InStrRev(sFormula, ","))
        sRange = Left(sRange, Len(sRange) - 1)
        
        '---> Update Col E
        sRange = Replace(sRange, "D", "E")
        WS.Cells(I, "E").Formula = "=COUNTIF(" & sRange & "," & Chr(34) & "Y" & Chr(34) & ")"
        
        '---> Update Col F
        sRange = Replace(sRange, "E", "F")
        WS.Cells(I, "F").Formula = "=COUNTIF(" & sRange & "," & Chr(34) & "Y" & Chr(34) & ")"
        
        '---> Update Col H
        sRange = Replace(sRange, "F", "H")
        WS.Cells(I, "H").Formula = "=COUNTIF(" & sRange & "," & Chr(34) & "N" & Chr(34) & ")"
       
        '---> Color Row in Yellow
        WS.Range("A" & I & ":H" & I).Interior.ColorIndex = 6
        WS.Range("A" & I & ":H" & I).Font.Bold = True
        lcountif = lcountif + 1        
    End If
Next I

MsgBox ("A total of " & lcountif & " Rows were updated with COUNTIF formula in Col E,F and H successfully.")

End Sub

Open in new window


I have  attached the workbook with a button starting the countif if you choose the option to do it manually.

I have not included your macro in the workbook attached as it is refering to sheet December12 that does not exist in the workbook.

If you have any question pls do not hesitate to ask.
Rgds/gowflow
Sample-Account-Spreadsheet.xlsm
0
 

Author Comment

by:Glenn Stearns
ID: 38787379
Gowflow...

That works beautifully!

I did note that, in the Macro (1), I had used a predefined range of lines 2 through 55 because that is how many lines the sheet had on it. However, with each run of this, there will be a different number of lines on the spreadsheet - could be 25; could be 200. How do I change the code to look for and find all the active lines on the spreadsheet, no matter how many there are, before running the InsertCOUNTIF Sub? Getting past this issue will make the whole code work excellently in any situation. I should have mentioned this in my initial post, but failed to do so. I apologize!

Thanks again!
Glenn
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 38787505
Glenn
I hv refined your routine and attached it to the new button to run all will run both yours (modified) + countif and I increased the sample to be 400 lines+ now no need to change the code wether 1 line 1000 or 100000000000000 lines !!!!

Enjoy
gowflow
Sample-Account-Spreadsheet.xlsm
0
 

Author Closing Comment

by:Glenn Stearns
ID: 38788059
Thanks so much! It runs beautifully!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question