Error 13 Type mismatch for Application.Evaluate

[VytautasM]

Good day,

Having trouble making a work project to work. Keep getting Error 13 Type mismatch for Application. Evaluate.

If Application.Evaluate(FC.Formula1) Then
ActiveCondition = Ndx

FC.Formula1 = AND($C8<>"";ISBLANK($AI8))

If I change the conditional formating to a single logic test the code seems to work. But I need this specific formula.

Whole code below:

Function ActiveCondition(Rng As Range) As Integer
    Dim Ndx As Long
    Dim FC As FormatCondition
    Dim Temp As Variant
    Dim Temp2 As Variant
    If Rng.FormatConditions.Count = 0 Then
        ActiveCondition = 0
    Else
        For Ndx = 1 To Rng.FormatConditions.Count
            Set FC = Rng.FormatConditions(Ndx)
            Select Case FC.Type
                Case xlCellValue
                Select Case FC.Operator
                    Case xlBetween
                        Temp = GetStrippedValue(FC.Formula1)
                        Temp2 = GetStrippedValue(FC.Formula2)
                        If IsNumeric(Temp) Then
                           If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
                               CDbl(Rng.Value) <= CDbl(FC.Formula2) Then
                               ActiveCondition = Ndx
                               Exit Function
                           End If
                       Else
                          If Rng.Value >= Temp And _
                             Rng.Value <= Temp2 Then
                             ActiveCondition = Ndx
                             Exit Function
                          End If
                       End If
                    Case xlGreater
                        Temp = GetStrippedValue(FC.Formula1)
                        If IsNumeric(Temp) Then
                           If CDbl(Rng.Value) > CDbl(FC.Formula1) Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        Else
                           If Rng.Value > Temp Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        End If
                    Case xlEqual
                        Temp = GetStrippedValue(FC.Formula1)
                        If IsNumeric(Temp) Then
                           If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
                               ActiveCondition = Ndx
                               Exit Function
                           End If
                        Else
                           If Temp = Rng.Value Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        End If
                    Case xlGreaterEqual
                        Temp = GetStrippedValue(FC.Formula1)
                        If IsNumeric(Temp) Then
                           If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
                               ActiveCondition = Ndx
                               Exit Function
                           End If
                        Else
                           If Rng.Value >= Temp Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        End If
                    Case xlLess
                        Temp = GetStrippedValue(FC.Formula1)
                        If IsNumeric(Temp) Then
                            If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
                               ActiveCondition = Ndx
                               Exit Function
                            End If
                        Else
                            If Rng.Value < Temp Then
                               ActiveCondition = Ndx
                               Exit Function
                            End If
                        End If
                    Case xlLessEqual
                        Temp = GetStrippedValue(FC.Formula1)
                        If IsNumeric(Temp) Then
                           If CDbl(Rng.Value) <= CDbl(FC.Formula1) Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        Else
                           If Rng.Value <= Temp Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        End If
                    Case xlNotEqual
                        Temp = GetStrippedValue(FC.Formula1)
                        If IsNumeric(Temp) Then
                           If CDbl(Rng.Value) <> CDbl(FC.Formula1) Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        Else
                           If Temp <> Rng.Value Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        End If
                    Case xlNotBetween
                        Temp = GetStrippedValue(FC.Formula1)
                        Temp2 = GetStrippedValue(FC.Formula2)
                        If IsNumeric(Temp) Then
                           If Not (CDbl(Rng.Value) <= CDbl(FC.Formula1)) And _
                              (CDbl(Rng.Value) >= CDbl(FC.Formula2)) Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        Else
                           If Not Rng.Value <= Temp And _
                              Rng.Value >= Temp2 Then
                              ActiveCondition = Ndx
                              Exit Function
                           End If
                        End If
                    Case Else
                        Debug.Print "UNKNOWN OPERATOR"
                End Select
            Case xlExpression
               If Application.Evaluate(FC.Formula1) Then
                  ActiveCondition = Ndx
                  Exit Function
               End If
          Case Else
                Debug.Print "UNKNOWN TYPE"
           End Select
        Next Ndx
    End If
    ActiveCondition = 0
End Function

Why am I getting, "Type mismatch" for Application.Evaluate - this thread explains a similar problem, but I do not understand the answer. A clarification of the answer if it applies to my problem would be great (The thread is old did not want to reopen).

Query on Conditional Formatting / Message Box - my initial code comes from here. Thank you contributor.
Conditional Formatting Colors - And originated here.

 

[Jon von der Heyden]

= AND($C8<>"";ISBLANK($AI8))

What values are in C8 and AI8 respectively, when the error is thrown?

 

[mikerickson]

It looks like FC is a range. Is it a one cell range and have you considered Evaluate("=" & FC.Address(,,,True)) as an alternative.

 

[VytautasM]

What values are in C8 and AI8 respectively, when the error is thrown?

C8 - 255685, AI8 - blank.

 

[VytautasM]

It looks like FC is a range. Is it a one cell range and have you considered Evaluate("=" & FC.Address(,,,True)) as an alternative.

Will be able to test only on Monday. Will let you know the result.

 

[VytautasM]

It looks like FC is a range. Is it a one cell range and have you considered Evaluate("=" & FC.Address(,,,True)) as an alternative.

Mike,

Just to clarify the whole code of line should look like this:

If Application.Evaluate("=" & FC.Address(,,,True)) Then

Correct ?

If so, the error then is:

I am still a novice at VBA so please bear with me.

 

[Jon von der Heyden]

So FC is a format condition correct? When it breaks, what does FC.Formula1 return? Could you print it to the immediate window before it tries to execute it? I recall something about it not processing relative references, or something to that effect?

 

[VytautasM]

So FC is a format condition correct? When it breaks, what does FC.Formula1 return? Could you print it to the immediate window before it tries to execute it? I recall something about it not processing relative references, or something to that effect?

Yes it is a format condition. In regards to the relative references, you might be right, changing the conditional formatting to a single reference solves the problem. But like I already mentioned need the double reference. Or an alternative with the same results.

 

[CSmith]

Yes it is a format condition. In regards to the relative references, you might be right, changing the conditional formatting to a single reference solves the problem. But like I already mentioned need the double reference. Or an alternative with the same results.

View attachment 5564
View attachment 5565

Why a ; in the formula?

Should it not read:

=AND($C8<>"",ISBLANK($AI8))

 

[Jon von der Heyden]

Yes it is a format condition. In regards to the relative references, you might be right, changing the conditional formatting to a single reference solves the problem.

I'm trying to recreate it but I cannot produce the same error. Clutching at straws, but is your activesheet the same sheet that has the format condition? If not:

FC.Parent.Parent.Evaluate(FC.Formula1)