Consulting

Results 1 to 15 of 15

Thread: VBA Help with Multiple If conditions

  1. #1

    VBA Help with Multiple If conditions

    Hello, I am trying to get VBA to assist with a counting operation in a multi-conditional checklist for goal tracking.

    The desired outcome is for the program is to check for a specific client’s name. Once that condition is met, I want it to check the data in another Column "MTD Count", and count each answer, in this case Yes, No, Refused, or Not Applicable individually onto that client’s sheet in the workbook. I.E. I should see how many times John Doe completed, did not complete, refused to complete, or the attempt was not applicable on that date for whatever reason. Then the program needs to move to the next client, in my example Pennywoth, on the list and repeat the process for all clients.

    The current issue in my coding is that the Function looks up the client name given in the function, but after it registers John Doe in the Client’s Name Column as true, it drops that as a prerequisite to the corresponding COUNTIF functions to follow, thus counting all Yes, No, Refusal, and Not Applicable. I need to program in a permanent condition of, If John Doe is in the client column, then to check and count the Yes/No/Refused/Not Applicable.

    In the future, there will be more fixed IF conditions that will have to be true to move to the next part of the function, but this is the first hurdle to overcome.

    Included are a snippet and sample of the code & Excel file being used.
    Sub CountYesJohn()
    With Sheets("Report_Results")
    Dim c As Range
    Dim b As Range
    Dim J As Integer




    For Each c In Range("B9:B679") 'Client #1 Loop
    If c.Value = "Doe, John" Then
    For Each b In Range("L9:L679")
    If b.Value = "Yes" Then
    J = Application.WorksheetFunction.CountIf(Range("L9:L679"), "Yes")
    Worksheets("JD").Range("B2") = J

    End If

    Next b
    End If
    Next c


    End With
    End Sub
    Attached Images Attached Images

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,825
    Location
    Sounds to me like it'd be an ideal application to use a pivot table

    Without a sample workbook with sufficient data, it's hard to make an example
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Well, that is where the issue currently lies. We are currently using a massive pivot table that we are constantly going in, editing, and ripping out data ourselves to populate the counts. It is an ugly mess and I am trying to create something that uses VBA, and not the current mess that then takes about an hour to digest and then input the data elsewhere. The goal is to move away from it for speed and we have found that Power BI, where the file hopefully will end up, has not liked the previous pivot table.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    You left out a lot of info. It appears you are trying to write a sub for each name

    I don't have a CountIfs in my version, so this is not tested
    Option Explicit
    Option Base 1 'For easier Arrays
    
    Enum ArrayIndices 'For clarity of code
        Individ = 1
        Yesses
        Noes
        Refusals
        NAs
    End Enum
    
    Sub CountMTDs()
    Const ReportSheet As String = "MTDsReport" 'Edit Name to suit
    Dim Names As New Scripting.Dictionary
    Dim Cel As Range
    Dim Report, Headers
    Dim i As Long
    
    'Create Unique list of Individuals
    With Sheets("Report_Resullts")
    For Each Cel In .Intersect(.UsedRange, Range("B:B"))
        On Error Resume Next
        Names.Add Cel, "True"
        Err = 0
    Next Cel
    End With
    
    'Check Conditon of Reports sheet
    SheetCondition ReportSheet 'See next Sub
    
    'Set Headers
    Headers = Array("Individual", "Yes", "No", "Refused", "Not Applicable")
    With Sheets(ReportSheet).Cells(1).Resize(1, UBound(Headers))
        .Value = Headers.Value
        .Font.Bold = True
        .Font.Size  = StandardFont.Size + 2
        .HorizontalAlignment = xlCenter
    End With
        
    'Create Report for each individual
    'Application.ScreenUpdating = False 'Remove Comment mark from beginning this line after testing
    With Sheets("Report_Resullts")
        For i = 1 To Names.Count
            ReDim Report(1 To UBound(Headers)) 'Reset and Clear array
            Report(Individ) = Names(i)
            Report(Yesses) = WorksheetFunction.CountIfs(.Range("B:B"), "=" & Names(i), .Range("L:L"), "=Yes")
            Report(Noes) = WorksheetFunction.CountIfs(.Range("B:B"), "=" & Names(i), .Range("L:L"), "=No")
            Report(Refusals) = WorksheetFunction.CountIfs(.Range("B:B"), "=" & Names(i), .Range("L:L"), "=Refused")
            Report(NAs) = WorksheetFunction.CountIfs(.Range("B:B"), "=" & Names(i), .Range("L:L"), "=Not Applicable")
            
            'Write the Report to the sheet
            Sheets(ReportSheet).Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(1, UBound(Headers)) = Report.Value
        Next
    End With
    
    'Resize the columns to fit
    Sheets(ReportSheet).Cells(1).CurrentRegion.Columns.AutoFit
    Application.ScreenUpdating = True    
    End Sub
    
    
    
    
    Private Sub SheetCondition(SheetToCheck As String)
    
    'True if False
    On Error Resume Next
    If Not Sheets(SheetToCheck).Name = SheetToCheck Then
        Sheets.Add Name = SheetToCheck
        Err = 0
    End If
    
    'If the sheet didn't exist, it does now
    Sheets(SheetToCheck).ClearContents
    'Display it
    Sheets(SheetToCheck).Activate
    End Sub
    Last edited by SamT; 04-28-2021 at 06:10 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,825
    Location
    Quote Originally Posted by tehpuppyryry View Post
    Well, that is where the issue currently lies. We are currently using a massive pivot table that we are constantly going in, editing, and ripping out data ourselves to populate the counts. It is an ugly mess and I am trying to create something that uses VBA, and not the current mess that then takes about an hour to digest and then input the data elsewhere. The goal is to move away from it for speed and we have found that Power BI, where the file hopefully will end up, has not liked the previous pivot table.

    OK, but my experience with pivot tables is that they're pretty fast and can answer almost any question

    I've used PTs to do the heavy lifting and then VBA to extract and format the data if more is required
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,187
    Post a sample workbook instead of irrelevant pictures.

    A slow pivottable means: an incompetent user.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,825
    Location
    Or maybe a user who is still learning the PT tricks and techniques that we've learned over the years by reading forums such as this which offer helpful advice
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    I tweaked the code in post# 4 a bit.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    Paul, his brain was engineered by Joel Spolsky.



    BTW, in a VBA Forum, that's a high compliment
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    Apologies for both the lack of clarity and time gap. This is my first time using a forum for help like this, so am a newbie at this and the headache of this has grown on me for over 2-months of approaching it.

    To explain the situation at the moment. Right now, we are pulling from an online program to fill a large pivot table, that has multiple pivot tables within it, that we look up, enter the pivot tables, and then get our results. The issue is that these pivot tables are not just one, but one for every client, with multiple factors that change the values and what/how it is calculating from inputs changing to the number of days in a month, all of which we must edit every time there is a change just to pull the data. Even more, the program we are looking up the data in to then copy to the pivot tables, has an excel export mode. All current pivot tables DO NOT MESH NOR COOPERATE with the Exportable Data. I.E. The Pivot tables, although being used currently, are only increasing the amount of time to get the data and are making things harder, rather than faster. So, my goal is to do away with the manual entering pivot tables, and format a VBA program that can do all the work for us from the exported Excel file, then be uploaded to Power BI. I repeat, I need to do this through VBA, not correct a past made pivot tables.

    So, for what I have been attempting to do. I have cleaned and included an example file this time. I have the Modules formatted in the last format I was trying to get it to work in. I have tried doing all of a client’s counts in one Sub, I.E. I tried to have it count all of John Doe’s Yes, No, Refused, and Not Applicable results in one sub and then place the data in John Doe’s Client sheet “JD”. When that failed, I formatted like this to do each result, yes/no/Refused/Not Applicable for John Doe was its own Sub. The issue I have discovered is that VBA drops the client’s name as a requirement for the counts of Yes, No, etc. once it sees the name in that column and defaults to a basic CountIF function for all data.

    Now, the main reason for the shift to VBA is that there is this hurdle to meet, and then more to evolve on this VBA program. After I have it to where it can check for Client name then count correlating results, I need to also program in even more requirements that must be met to validate the counting. To give an example, from my example file, there is only one Goal type, in Outings. There will be more goal types, and I will in the future need it to differentiate between Yes’s when John Doe went on an Outings goal versus a Health goal. So, I will need to have a system that can have two or more prerequisites being met before it counts that data.
    Attached Files Attached Files

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,335
    Your CountYesJohn macro can be reduced to:
    Sub CountYesJohn()
    With Sheets("Report_Results")
      Worksheets("JD").Range("B2") = Application.CountIfs(.Range("B9:B679"), "Doe, John", .Range("L9:L679"), "Yes")
    End With
    End Sub
    It should be easy to adjust your other macros.
    But does this need a macro? You can put the formula:
    =COUNTIFS(Report_Results!$B$9:$B$679,"Doe, John",Report_Results!$L$9:$L$679,"Yes")
    in cell B2 of sheet JD.

    It can be easier than that; if you have in cell B1 of the JD sheet "Doe, John" instead of the current "John Doe", your formula could become:
    =COUNTIFS(Report_Results!$B$9:$B$679,$B$1,Report_Results!$L$9:$L$679,"Yes")
    which means the formula in cell B2 wouldn't change from sheet to sheet.

    One last question, I see you have the words 'Filtered Yes Count'; does this mean you're going to filter the table in the Report_Results sheet at some point and only want a count of the visible results?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    I take it that you haven't tried the code in post #4 yet.

    Place it in a module by itself, run CountMTDs and lets us know if any issues occur and what they are.

    PS: I just now changed the name of sheets("JD") to Report_Results in that code

    To explain the situation at the moment. Right now, we are pulling from an online program to fill a large pivot table, that has multiple pivot tables within it, that we look up, enter the pivot tables, and then get our results. The issue is that these pivot tables are not just one, but one for every client, with multiple factors that change the values and what/how it is calculating from inputs changing to the number of days in a month, all of which we must edit every time there is a change just to pull the data. Even more, the program we are looking up the data in to then copy to the pivot tables, has an excel export mode. All current pivot tables DO NOT MESH NOR COOPERATE with the Exportable Data. I.E. The Pivot tables, although being used currently, are only increasing the amount of time to get the data and are making things harder, rather than faster. So, my goal is to do away with the manual entering pivot tables, and format a VBA program that can do all the work for us from the exported Excel file, then be uploaded to Power BI. I repeat, I need to do this through VBA, not correct a past made pivot tables.
    If the goal is to work on the Exportable Data, why are you showing us only the data from one pivot table? Asking for help on one process at a time is going to leave you with a Project held together with baling wire, duct tape, and spit.

    What I think you want is Exportable Data >> Code >> Reports, where Reports is what your people need to see. It might be easiest and best for you to go from Code to Intermediate Data which you can use to create your own Reports from.

    For us to really help you towards your ultimate goal, we will need to see a few samples of the Exportable Data and examples of any Reports you want from it. Knowing the "Multiple Factors" is also important.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #13
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    Since you are acting as the Project Manager for this, I suggest you spend an hour tonight reading the four parts of https://www.joelonsoftware.com/2000/...-1-why-bother/

    BTW, Joel was the main Microsoft guy responsible for VBA
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #14
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,187
    No comment
    Attached Files Attached Files

  15. #15
    Thank you! I apologize for lack of response. I have had a massive deadline to meet and now am finally above water enough to work on this. Thank you both SamT & P45cal. These actually work, are what I asked for, and get things moving and resolved for me! And SamT, your questions were on the mark of what I am doing. I had to format it like this for professional reason, but the table was the closest to what is spit out by our DDO. From that there is a lot of redundant data that I need to ignore and wanted to code some means of quicker data pulls than what we were doing of filter the table, and write the numbers on paper, to then transfer to a shared online file. I had figured all other factors before hand to where having the code produce the results I wanted was my last hurdle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •