VBA Help with Multiple If conditions

[tehpuppyryry]

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

[Paul_Hossler]

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

[tehpuppyryry]

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.

[SamT]

You left out a lot of info. It appears you are trying to write a sub for each name:crying:

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

[Paul_Hossler]

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

[snb]

Post a sample workbook instead of irrelevant pictures.

A slow pivottable means: an incompetent user.

[Paul_Hossler]

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

[SamT]

I tweaked the code in post# 4 a bit.

[SamT]

Paul, his brain was engineered by Joel Spolsky. :rofl:



BTW, in a VBA Forum, that's a high compliment

[tehpuppyryry]

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.

[p45cal]

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?

[SamT]

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.

[SamT]

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/10/02/painless-functional-specifications-part-1-why-bother/

BTW, Joel was the main Microsoft guy responsible for VBA

[snb]

No comment

[tehpuppyryry]

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.