InStr method not working

[NessPJ]

Hi all,

I have a value in Cell A1 of:
HBST-MC022 1551401715514017AAA202007171210167738324000000L0511011111111

I am using the following code to try and determine if the string contains "HBST-MC022".
Yet my code keeps returning 0. When the first Cell actually contains the value. Am i doing something wrong?

Private Sub MC022Test()

Dim CheckMC022 As Long

'Set variables used for all interfaces to process
    Set Rng = Sheets("i21").Range("A1:A1000")
 
'Loop each cell in range
    For Each Cell In Rng.Cells

    WriteCell = Cell.Offset(0, 12).Address

    CheckMC022 = InStr(0, Cell.text, "HBST-MC022", vbTextCompare)

    Debug.Print CheckMC022

End Sub

 

[Fluff]

The first argument of the InStr should be a minimum of 1. You cannot start check from character 0 as it doesn't exist.

 

[Peter_SSs]

Your code is incomplete as you have For Each Cell ..

but you do not have

Next Cell


In any case, try changing to
CheckMC022 = InStr(1, Cell.text, "HBST-MC022", vbTextCompare)

 

[NessPJ]

Your code is incomplete as you have For Each Cell ..

but you do not have
Next Cell

In any case, try changing to
CheckMC022 = InStr(1, Cell.text, "HBST-MC022", vbTextCompare)

Hello Fluff and Peter_SSs,

Thanks for your replies.
Leaving out the 'Next' statement was a copying error on my part.

I tried both your suggestions, but for the positive line the returned value is still '0'.
With the following value: HBST-MC022 1551401715514017AAA202007171210167738324000000L0511011111111
I would expect a return of 1 when the substring was actually found, yes?

This is the entire code i have in there now:

Private Sub MC022Test()

Dim CheckMC022 As Long
Dim Msg As String

PROTOFF (Password)

Sheets("i21").Activate

'Set variables used for all interfaces to process
    Set Rng = Sheets("i21").Range("A1:A1000")
 
'Loop each cell in range
    For Each Cell In Rng.Cells

    WriteCell = Cell.Offset(0, 12).Address

    CheckMC022 = InStr(1, Cell.text, "HBST-MC022", vbTextCompare)
    Debug.Print CheckMC022
    If CheckMC022 > 0 Then  
    '// Nothing here yet
    End If
   
    Next

End Sub

 

[Fluff]

You will get a 0 if the string is not found & the position of the first character of the string if it is found.
If you are getting a 0 then the string was not found in the cell.

 

[NessPJ]

You will get a 0 if the string is not found & the position of the first character of the string if it is found.
If you are getting a 0 then the string was not found in the cell.

I just tried something different and now its working all of a sudden!
If i use

Set Rng = Sheets("i21").Range("A1:A1000")

Debug.Print will show me nothing but zero's.
If i use

Dim MC022LR As Long     'LR = LastRow
MC022LR = Sheets("i21").Range("A65534").End(xlUp).Row
Set Rng = Sheets("i21").Range("A1:A" & MC022LR)

It's suddenly working fine and the Debug.Print shows zero's and one's in the immediate window.

Is there a maximum number of lines the immediate window will show perhaps?

 

[Fluff]

I think the immediate window only shows 200 lines.