Create a Macro to Calculate Percentages

[Huizar]

I'm new to the macro world but needs some help to create a macro that will populate multiple percentages. My raw data looks like the following:

Column 1 Column 2
[TABLE="width: 599"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dispatched Service DEER 1[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode 1 DEER LAB[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode A - CU doesn't want to T/S DEER 3[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Mode B - CU not knowledgable to T/S DEER 5.2 [/TD]
[/TR]
[TR]
[TD]Dispatched Service - GYM 20 - No Parts to T/S DEER PRO[/TD]
[/TR]
[TR]
[TD]Dispatched Service - CRY 21 - No Time to T/S ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service - Tech69 - Ongoing/Repeat Issue ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service - With Help ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service with Tier 1 Support ETC[/TD]
[/TR]
[TR]
[TD]Dispatched Service with Tier 2 Support ETC[/TD]
[/TR]
[TR]
[TD]Dispatched ServiceSent im to Lin[/TD]
[/TR]
[TR]
[TD]Field Assistance[/TD]
[/TR]
[TR]
[TD]Phone Fix[/TD]
[/TR]
[TR]
[TD]Phone Fix - With Help[/TD]
[/TR]
[TR]
[TD]Phone Fix with Tier 1 Support[/TD]
[/TR]
[TR]
[TD]Phone Fix with Tier 2 Support[/TD]
[/TR]
[TR]
[TD]Referred[/TD]
[/TR]
[TR]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
I need to calculate the % Tier Use for specific machinery. For example, My total "Dispatched" sum is about 332. Key word is "Dispatched" regardless of words that follow. Then I need to combine the Dispatched Service w/ Tier 1, Tier 2, etc with Phone fix w tier 1, tier 2, which roughly gives me sum of lets say 34. Keep in mind my list may grow every week. So the key word is "Tier" regardless of the words that follow. My % Tier Use would be 334/32= 10.2%. And I'm only evaluating the tier use for specific machinery. For example, DEER 1, DEER 3, DEER LAB, etc. All the DEERS would fall under the Tier Use of 10.2%. I have to run this calculation for 6 different specific machinery. The key things is I need to get the Tier USE %. And my list may grow so the rows may extend since i am pulling data every week.

Any Ideas? The help is truly appreciated.

Thanks

 

[Huizar]

Thanks Veritan. I do have one more question. I put the code in and it says over flow run time error. I'm really new to the macro world, so I don't necessarily understand. what is going on. When i go to debug it highlights ---> .Offset(, 1) = Tier / Dispatched.

Thanks

 

[Veritan]

Did you use the code that I've currently got in my last post? I accidentally copied an older version of the code at first which didn't have error handling in it for when Dispatched was 0. I noticed it and edited my post, but you may have copied it before I made the edit. Make sure you're using what's in there now (and change the one line that you asked about regarding blanks). I think the Overflow error is being caused by trying to divide by 0. Excel doesn't handle imaginary numbers too well :P

 

[Huizar]

Ah, it works. I appreciate your patients. Is there anyway I can make the Models test condensed to a key word rather than typing every model type in the code? That is a long list i have.

so for instance rather than having to type the variation of DEER XXX, can i just set up it where it just finds all DEER regardless of the text characters that follow?

Thanks

 

[Veritan]

Glad to hear it's starting to work for you. As far as using a key word, it should already be able to handle that. Just put the single key word in the Array list and it will look for anything with that key word in it. You should only need to have the 6 names of your machines in there, and it should look for any occurrence of them. So rather than putting DEER XXX in the list, just put "deer" and it will look for any occurrence of DEER in column A. If you put a specific machine name in there like DEER LAB, it will only give you information regarding the DEER LAB machine and not any of the others.

 

[Huizar]

Hey Veritan, I spoke to soon on my previous comment. The macro does exactly what I need it to do. I appreciate it. Thanks for your patients I'm a total newbie at macro stuff. Is there a line of code that I can add to add two models together (i.e Tiger & Octopus)? And a code to get the total % Tier Use combined among all the models that I am looking at? For instance, below is what the calculation gave me based on macro. How can i add the two bolded lines:

[TABLE="width: 317"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Tier Use %: DEER[/TD]
[TD="align: right"]5.8%[/TD]
[/TR]
[TR]
[TD]Tier Use %: OCT[/TD]
[TD="align: right"]4.8%[/TD]
[/TR]
[TR]
[TD]Tier Use %: VEL[/TD]
[TD="align: right"]6.5%[/TD]
[/TR]
[TR]
[TD]Tier Use %: TIG & GIF [/TD]
[TD]X.X%[/TD]
[/TR]
[TR]
[TD]Total Tier Use %:[/TD]
[TD]X.X%[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[Veritan]

This code should add the lines that you want. I also set it so that if you want to add more subtotals than just TIG & GIF, it should be fairly simple to do. The green lines are instructions on how to adjust the code as needed in case you want to add more subtotals. If you don't, however, the code will be fine as it is and doesn't need adjusted.

Sub Percentage()
    Dim r As Range, i&, Dispatched&, Tier&, arrMach() As Variant, FirstSumRow&, LastSumRow&, Tig, Gif
    
[COLOR=#008000]    'Make sure to enter the names of the machines in lowercase here in order to be able to match all case types in the data.[/COLOR]
    arrMach = Array("deer", "turtle", "squid", "kangaroo", "octopus", "elephant")
    For i = 0 To UBound(arrMach)
[COLOR=#008000]        'Change the cell references below to be the column and row where your data starts.[/COLOR]
        For Each r In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If InStr(LCase(r), arrMach(i)) <> 0 And InStr(LCase(r.Offset(, 1)), "dispatched") <> 0 Then Dispatched = Dispatched + 1
            If InStr(LCase(r), arrMach(i)) <> 0 And InStr(LCase(r.Offset(, 1)), "tier") <> 0 Then Tier = Tier + 1
        Next r
        With Cells(Rows.Count, 1).End(xlUp).Offset(1)
            .Value = "Tier Use %: " & UCase(arrMach(i))
            If Dispatched = 0 Then
                .Offset(, 1) = 0
            Else: .Offset(, 1) = Tier / Dispatched
            End If
            .Offset(, 1).NumberFormat = "0.0%"
        End With
        Tier = 0
        Dispatched = 0
    Next i
    FirstSumRow = Cells(Rows.Count, 1).End(xlUp).Offset(-5).Row
    LastSumRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range(Cells(Rows.Count, 1).End(xlUp).Offset(-5), Cells(Rows.Count, 1).End(xlUp))
[COLOR=#008000]        'Put any other machine Tier use values you want to find in here following the same format.[/COLOR]
[COLOR=#008000]        'Make certain that you put the period in front of the Find command, it's easy to forget it's there.[/COLOR]
[COLOR=#008000]        'If you add a machine name to this list, add the variable name to the Dim row at the top, making sure to separate them by commas.[/COLOR]
        Set Tig = .Find("TIG").Offset(, 1)
        Set Gif = .Find("GIF").Offset(, 1)
    End With
    With Cells(Rows.Count, 1).End(xlUp)
[COLOR=#008000]        'The Offsets below are used to put the information in the right cell, starting from the last "Tier Use%:" line.[/COLOR]
[COLOR=#008000]        'If you want to add more subtotal amounts, follow the same format as the 2 lines directly below, but adding 1 to the[/COLOR]
[COLOR=#008000]        'first number in the offset each time (the 1 in the Offset command with only one number is the one that will be adjusted).[/COLOR]
        .Offset(1) = "Tier Use %: TIG & GIF"
        .Offset(1, 1) = Tig + Gif
[COLOR=#008000]        'If you've added any other subtotal lines, make sure the 2 lines below this one are the last code lines in this section.[/COLOR]
[COLOR=#008000]        'Adjust the 2 in the lines below to be the last in the sequence (2, 3, 4, etc.)[/COLOR]
        .Offset(2) = "Total Tier Use %:"
        .Offset(2, 1) = WorksheetFunction.Sum(Range(Cells(FirstSumRow, 2), Cells(LastSumRow, 2)))
    End With
End Sub

The code assumes that you have all 6 machines listed in the array. If not, the offset reference in the FirstSumRow line will need to be adjusted to be the machines you are using minus one, and make sure the number is negative. The With statement 2 lines under the FirstSumRow will need to have the Offset value in it changed to be the same number, too.

Don't worry about being new to code, it's a little daunting at first. You'll get better as you practice. If you'd like to learn more about it, there's a great video series on YouTube that I used when I was first learning VBA. I'll link the first video below if you'd like to check it out.

https://www.youtube.com/watch?v=KHO5NIcZAc4&list=PLlnRZGIp4JUHNbPnB5MFsibNiyUQQGgPU

 

[Huizar]

Hey Varitan,

This is great. I appreciate the super help. Yes, I will definitely check out the youtube link. Thanks once again.

 

[Veritan]

You're welcome, glad it worked out for you. Best of luck!

 

[Huizar]

Hey Veritan,

Happy Monday. So I went to the youtube link you sent me and did a bit of testing. Thanks for the link. So the macro, i was running. I think you misunderstood on the last two lines of code; I probably didn't explain myself well. On the Total tier% Use it adds all the percentages together, which is not the result i'm looking for. I'm looking for all all the combined models total tier count within all modesl/ divided by total dipatch count of all model. I'm looking for an aggregated percentage. When I did the raw data calculation the the percentage was a lot lower than what the macro resulted. The macro essentially added all the percentages. And the "TIG & GIF percentage" is a made of two models that i need to combined. which are seperate from DEER, OCT, and VEL.

[TABLE="class: cms_table, width: 317"]
<tbody>[TR]
[TD][TABLE="width: 282"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Tier Use %: DEER[/TD]
[TD="align: right"]5.8%[/TD]
[/TR]
[TR]
[TD]Tier Use %: OCT[/TD]
[TD="align: right"]4.8%[/TD]
[/TR]
[TR]
[TD]Tier Use %: TIG[/TD]
[TD="align: right"]26.5%[/TD]
[/TR]
[TR]
[TD]Tier Use %: GIF[/TD]
[TD="align: right"]8.4%[/TD]
[/TR]
[TR]
[TD]Tier Use %: VEL[/TD]
[TD="align: right"]6.5%[/TD]
[/TR]
[TR]
[TD]Tier Use %: TIG/GIF[/TD]
[TD="align: right"]35%[/TD]
[/TR]
[TR]
[TD]Total Tier Use %:[/TD]
[TD="align: right"]125%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Veritan]

Hello Huizar. I think this is what you're looking for. It's also pretty easy to edit, in case you need to work with it. Keep an eye on the variable declaration and offset values if you need to change it any.

Sub Percentage()
    Dim r As Range, i&, Dispatched&, Tier&, arrMach() As Variant, DeerTier&, DeerDisp&
    Dim OctTier&, OctDisp&, TigTier&, TigDisp&, GifTier&, GifDisp&, VelTier&, VelDisp&
    
    arrMach = Array("deer", "oct", "tig", "gif", "vel")
    For i = 0 To UBound(arrMach)
        For Each r In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If InStr(LCase(r), arrMach(i)) <> 0 And InStr(LCase(r.Offset(, 1)), "dispatched") <> 0 Then Dispatched = Dispatched + 1
            If InStr(LCase(r), arrMach(i)) <> 0 And InStr(LCase(r.Offset(, 1)), "tier") <> 0 Then Tier = Tier + 1
        Next r
        With Cells(Rows.Count, 1).End(xlUp).Offset(1)
            .Value = "Tier Use %: " & UCase(arrMach(i))
            If Dispatched = 0 Then
                .Offset(, 1) = 0
            Else: .Offset(, 1) = Tier / Dispatched
            End If
            .Offset(, 1).NumberFormat = "0.0%"
        End With
        Select Case arrMach(i)
            Case "deer"
                DeerTier = Tier
                DeerDisp = Dispatched
            Case "oct"
                OctTier = Tier
                OctDisp = Dispatched
            Case "tig"
                TigTier = Tier
                TigDisp = Dispatched
            Case "gif"
                GifTier = Tier
                GifDisp = Dispatched
            Case "vel"
                VelTier = Tier
                VelDisp = Dispatched
        End Select
        Tier = 0
        Dispatched = 0
    Next i
    With Cells(Rows.Count, 1).End(xlUp)
        .Offset(1) = "Tier Use %: TIG/GIF"
        .Offset(1, 1) = (TigTier + GifTier) / (TigDisp + GifDisp)
        .Offset(1, 1).NumberFormat = "0.0%"
        .Offset(2) = "Total Tier Use %:"
        .Offset(2, 1) = (DeerTier + OctTier + TigTier + GifTier + VelTier) / (DeerDisp + OctDisp + TigDisp + GifDisp + VelDisp)
        .Offset(2, 1).NumberFormat = "0.0%"
    End With
End Sub