CODEWITHSUNDEEP
excelvba
Matt Lane
Matt Lane

Reputation: 97

VBA Nesting countif inside of If statement

Using VBA I have a countifs formula that returns a number depending on the value cell F3. F3 is a data validation. I am able to pick individual cases out with my formula, but I am having trouble making an "all" values selection. Researching I came up with "*" as my value to pull everything (the wildcard). Conditional formatting wont let me mask the name as something else due to it being a star. Any ideas on how to display the name as something else, or changing my formula to pull all names in column AF? Thank you very much! Matt. 

Sheets("Macro").Range("M54") = _
    Application.WorksheetFunction.CountIfs(.Range("E5:E" & finRow), _
         "=Addition", .Range("N5:N" & finRow), _
         "=" & Sheets("Macro").Range("$D$3").Value, .Range("AF5:AF" & finRow), _
         "=" & Sheets("Macro").Range("$F$3").Value)

Upvotes: 0

Views: 510

Answers (1)

Tim
Tim

Reputation: 48

If I'm understanding your issue correctly (which is that your data validation list in cell F3 is currently working properly with your VBA code, but you're looking for a way to display the asterisk as another word like "All"), then I would suggest changing your data validation list from displaying the asterisk to displaying "ALL" and editing your VBA to accommodate that change. For example:

If Sheets("Macro").Range("F3").Value = "ALL" Then

Sheets("Macro").Range("M54") = _
    Application.WorksheetFunction.CountIfs(.Range("E5:E" & finRow), _
         "=Addition", .Range("N5:N" & finRow), _
         "=" & Sheets("Macro").Range("$D$3").Value, .Range("AF5:AF" & finRow), _
         "=*")
Else

Sheets("Macro").Range("M54") = _
    Application.WorksheetFunction.CountIfs(.Range("E5:E" & finRow), _
         "=Addition", .Range("N5:N" & finRow), _
         "=" & Sheets("Macro").Range("$D$3").Value, .Range("AF5:AF" & finRow), _
         "=" & Sheets("Macro").Range("$F$3").Value)
End If

Upvotes: 1

Related Questions