InStr method not working

NessPJ

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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:
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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
VBA Code:
Set Rng = Sheets("i21").Range("A1:A1000")
Debug.Print will show me nothing but zero's.
If i use
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
I think the immediate window only shows 200 lines.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top