InStr gives variable results

[hstaubyn]

Hi,

I have a slight problem using InStr in VBA, code below:

Dim Text1, Text2, Text3 as String
 
For Each iRange In Range("Org_List")
    If InStr(1, iRange.Value, Text1) <> 0 And Text1 <> "" Then
        Call Insert_Checkbox(iRange.Row)
    Else
        If InStr(1, iRange.Value, Text2) <> 0 And Text2 <> "" Then
            Call Insert_Checkbox(iRange.Row)
        Else
            If InStr(1, iRange.Value, Text3) <> 0 And Text3 <> "" Then
                Call Insert_Checkbox(iRange.Row)
            End If
        End If
    End If
Next iRange

The 3 different text values are taken from cells on the worksheet.

The problem is that if the text value appears at the start of an iRange, sometimes InStr gives '0' as its position in the string. I do not know why this would be, surely it should be '1'?? Also, sometimes if the text value is a single letter say, which appears more than once, InStr can give different results, seemingly depending on what mood it's in...

This is causing issues because even when the text does appear in iRange, it returns a '0' and then doesn't call the Insert_Checkbox sub. Is this a problem with InStr or have I missed something?

Thanks,
H

 

[VoG]

It may be related to the way you are declaring your variables. To Dim them all as strings

Dim Text1 As String, Text2 As String, Text3 As String

 

[hstaubyn]

Thanks for the reply,

I changed it and that didn't do anything...

I think it might be something to do with InStr not knowing which position of text1 etc. in the subject string to shoose from if there are several, but that would not explain why it returns '0' when the text is at the start of the string.

 

[pgc01]

Hi

If InStr returns 0 it's because the text was not found.

Maybe a problem with spaces, invisible characters, case sensitivity?

Please post examples.

 

[hstaubyn]

Ahh, is InStr case sensitive? Didn't realise... I will try including UCase into the code.

it works now, thanks for the help.

Dim Text1, Text2, Text3 as String
 
For Each iRange In Range("Org_List")
    If InStr(1, UCase(iRange.Value), UCase(Text1)) <> 0 And Text1 <> "" Then
        Call Insert_Checkbox(iRange.Row)
    Else
        If InStr(1, UCase(iRange.Value), UCase(Text2)) <> 0 And Text2 <> "" Then
            Call Insert_Checkbox(iRange.Row)
        Else
            If InStr(1, UCase(iRange.Value), UCase(Text3)) <> 0 And Text3 <> "" Then
                Call Insert_Checkbox(iRange.Row)
            End If
        End If
    End If
Next iRange

 

[Peter_SSs]

UCase is a good way to go. However, if you are not dealing with unusual (ie unusual to us standard English users) text (ref) then you could use the 4th (optional) argument for InStr.

For example

Sub test()
    Dim LookInVal As String, LookForVal As String
    
    LookInVal = "AbC"
    LookForVal = "B"
    
    MsgBox InStr(1, LookInVal, LookForVal)
    MsgBox InStr(1, LookInVal, LookForVal, vbTextCompare)
End Sub

 

[pgc01]

Hi

I'm glad it's working.

Another way, if in that particular module you are sure that you want all text comparisons not to be case sensitive, you just have to add at the beginning of the module:

Option Compare Text