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
Reply With Quote