Search code examples
excelvbaevaluate

excel vba countifs evaluate

I am getting an error on my evaluate function.

This code is running on a test sheet, so I know that it should give me "1" as answer, but I cannot make it run.

Here are the codes;

Check1 = Worksheets(Persona).Range("A3:A" & LastRowE3 & "")
Check2 = Worksheets(Persona).Range("J3:J" & LastRowE3 & "")
Ur_Val = "Production_End"


y = Application.Evaluate("=COUNTIFS(" & Check1 & ", " & xu_value & ", " & Check2 & ", " & Ur_Val & ")")

I know that "y" should be equal to "1", but I cannot get the answer right.

Solution

  • first declare your variables:

    Dim Check1 as String,Check2 as String,Ur_Val as String,xu_value as String

    Then

    You want the Address of the ranges not just the range values:

    Check1 = Worksheets(Persona).Range("A3:A" & LastRowE3 & "").Address(1,1,,1) Check2 = Worksheets(Persona).Range("J3:J" & LastRowE3 & "").Address(1,1,,1)

    And the strings Ur_Val and xu_value need to be surronded in " in the final formula so we need to add them:

    , """ & xu_value & """,

    So:

    Dim Check1 as String,Check2 as String,Ur_Val as String,xu_value as String
Dim y as Long
Check1 = Worksheets(Persona).Range("A3:A" & LastRowE3 & "").Address(1,1)
Check2 = Worksheets(Persona).Range("J3:J" & LastRowE3 & "").Address(1,1)
Ur_Val = "Production_End"
xu_value = "SOMETHING_ELSE"


y = Application.Evaluate("=COUNTIFS(" & Check1 & ", """ & xu_value & """, " & Check2 & ", """ & Ur_Val & """)")