VBA Instr - not giving correct result

[Mallesh23]

Hi Team,

Need help , Instr function not working as per my expectation.
I want loop to go inside if not found [ does not contain )

How to search multiple value does not contain using instr. Can we reduce code here.

Option Explicit

Sub test()

Dim str As String
str = "Fruit - [B]Coconut[/B]"

If InStr(1, str, "apple", vbTextCompare) = 0 Or _
    InStr(1, str, "Mango", vbTextCompare) = 0 Or _
    InStr(1, str, "Chikoo", vbTextCompare) = 0 Or _
    InStr(1, str, "WaterMellon", vbTextCompare) = 0 Or _
    InStr(1, str, "Banana", vbTextCompare) = 0 Then
    
    MsgBox str & " not Found"

Else

    [B]MsgBox str & " not Found"  'msgbox "fruit Not Found"[/B]

End If

End Sub

Thanks
mg

 

[jasonb75]

A couple of options for you to look at,

Option Explicit

Sub test()

Dim str As String
str = "Fruit - coconut"

If InStr(1, str, "apple", vbTextCompare) Or _
    InStr(1, str, "Mango", vbTextCompare) Or _
    InStr(1, str, "Chikoo", vbTextCompare) Or _
    InStr(1, str, "WaterMellon", vbTextCompare) Or _
    InStr(1, str, "Banana", vbTextCompare) Then
    
    MsgBox str & " Found"

Else

    MsgBox str & " Not Found"

End If

End Sub

Sub test2()

Dim str As String, fruits As Variant, a As Long, foundit As Boolean
str = "Fruit - coconut"
fruits = Array("Apple", "Mango", "Chikoo", "WaterMellon", "Banana")

For a = 0 To UBound(fruits)
    If InStr(1, str, fruits(a), vbTextCompare) Then
        
        MsgBox str & " Found"
        foundit = True
        Exit For
    End If
Next
If foundit = False Then MsgBox str & " Not Found"

End Sub

 

[GWteB]

It's not clear to me what what should be found, and where. The messages in the code below are reflecting what you are trying to do.

Sub test()
    Dim str As String
    str = "Fruit - [B]Coconut[/B]"

    If InStr(1, str, "apple", vbTextCompare) = 0 Or _
        InStr(1, str, "Mango", vbTextCompare) = 0 Or _
        InStr(1, str, "Chikoo", vbTextCompare) = 0 Or _
        InStr(1, str, "WaterMellon", vbTextCompare) = 0 Or _
        InStr(1, str, "Banana", vbTextCompare) = 0 Then
   
        MsgBox "None of the five previous fruits have been found in the string [" & str & "]"

    Else
        MsgBox "At least one of the five previous fruits have been found in the string [" & str & "]"

    End If
End Sub

 

[Mallesh23]

Hi Jason!

Thanks for your help, both approach worked as expected ! ?

whats wrong in my code , Both are giving found... I want it should go inside the loop

Sub test()

Dim str As String
str = "Fruit - Apple" or
str = "Fruit - Coconut"

If InStr(1, str, "apple", vbTextCompare) = 0 Or _
    InStr(1, str, "Mango", vbTextCompare) = 0 Or _
    InStr(1, str, "Chikoo", vbTextCompare) = 0 Or _
    InStr(1, str, "WaterMellon", vbTextCompare) = 0 Or _
    InStr(1, str, "Banana", vbTextCompare) = 0 Then
   
    MsgBox "FOUND"

End If


End Sub

Thanks
mg

 

[jkpieterse]

Change the "Or"-s to "AND".

 

[jasonb75]

The Or and = 0 are cancelling each other out, you either need to remove the = 0 parts or use And instead of Or.

 

[Mallesh23]

Hi All,

Thank you all for your help, I got it now its logic.

is it possible to to write all substrings in a single line.

if InStr(1, str, "apple;mango;Chikoo;Banana;waterMelon", vbTextCompare) >0 then

end if


Thanks
mg

 

[jasonb75]

is it possible to to write all substrings in a single line

The closest that you will get is either to use the second method that I suggested earlier, or to evaluate the worksheet search function as a formula (which will likely be slower to process).

 

[Mallesh23]

Hi Jason,

Thanks for you for your help. I Will convert it into function. Thanks ?



Thanks
mg

 

[Alex Blakenburg]

Hi All,

Thank you all for your help, I got it now its logic.

is it possible to to write all substrings in a single line.

if InStr(1, str, "apple;mango;Chikoo;Banana;waterMelon", vbTextCompare) >0 then

The solutions above are the most robust but if you can rely on the format consistently being "Fruit - Apple" and that you are always looking for the end part after the last dash ("-") eg "Apple" then you could split out the last part of str and reverse your instr to
InStr(1, "apple;mango;Chikoo;Banana;waterMelon", str, vbTextCompare) >0
Where str is just the apple part of "Fruit - Apple"