Compare a string against a set of strings

[megahurtz]

Hi All,

I have to compare a string against a set of substrings.

However, the number of substrings to be matched with is not fixed. Thereby I'm confused how to write my IF statement.

For eg. if the string in tbl_nm is to be compared against these substrings FACT*, STG*, DIM*, the code would look like:

sub test()

tbl_nm = "FACT_CONTROL_ABC"

If tbl_nm Like "FACT*" or tbl_nm Like "STG*" or tbl_nm Like "DIM*" then
' Do Something
End If

end sub

There may be even different substrings to be compared with. So the IF statement can't be hard-coded. How do I write the IF logic effectively?????

Please Help. Thanks a lot in advance...

 

[mikerickson]

Dim flag As Boolean
Dim onePattern as Variant

tbl_nm = "FACT_CONTROL_ABC"

flag = False
For Each onePattern in Array("FACT*", "STG*", "DIM*")
    flag = flag Or (tbl_nm Like onePattern)
Next onePattern

If flag Then
    Rem do something
End If

 

[megahurtz]

Thanks a lot Mike. Will try that...

 

[brettdj]

A suggested small amendment below. You can exit the test once you have a True result rather as further testing is redundant - this may make a significant time difference depending on how many comparisons you are running

hth

Dave

   Dim bFlag As Boolean
    Dim onePattern As Variant
    tbl_nm = "FACT_CONTROL_ABC"
    bFlag = False
    For Each onePattern In Array("FACT*", "STG*", "DIM*")
        If tbl_nm Like onePattern Then
            bFlag = True
            Exit For
        End If
    Next onePattern

    If bFlag Then
        Rem do something
    End If

 

[megahurtz]

Mike n Bret,

Thanks a lot for ur ideas guys! They worked like a charm for me...