VBA Alternative for COUNTIF

[Derrick Rikley]

Hi Everyone,

I have searched for a solution to this and can't find anything anywhere.

I have the following code working in a cell and now I need to move it into a macro.

=IF(COUNTIF(X2,"*Target*"),1,IF(COUNTIF(X2,"*Critical*"),1,IF(COUNTIF(X2,"*Virtual*"),1,)))

Does anyone have any ideas?

Thanks!

D.

 

[Sam Mathai Chacko]

This is one way of doing it!

    If InStr(1, "targetcriticalvirtual", Range("X2").Value, vbTextCompare) > 0 Then
        MsgBox 1
    Else
        MsgBox False
    End If

 

[Derrick Rikley]

Thanks Sam! I need the value of 1 populating a column on the same line that the code will be evaluating.

 

[Sam Mathai Chacko]

Ah! OK. You can use the following function as a UDF in Excel. First you need to paste this in a code module (insert a new module just to be on the safer side)

Function CHECKINSTRING(strVal As String) As Long

    If InStr(1, "targetcriticalvirtual", strVal, vbTextCompare) > 0 Then
        CHECKINSTRING = 1
    End If
    
End Function

Then in the Cell of the corresponding row, use this as a function =CHECKINSTRING(X2)

 

[shrivallabha]

I don't see any benefit of using UDF where a native function is working. In above case, UDF will be slower.

 

[SirJB7]

Hi, Derrick Rikley!
I agree with shrivallabha but if despite of that you still need to do it by VBA, you can call the original Excel function from VBA using the Application.WorksheetFunction object:
=IF(Application.WorksheetFunction.Countif...
Regards!