Finding Text With in Text

[prajul89]

Is there a way to find out whether 7 is included in 1237654 or not in VBA without using Error handler?

 

[T. Valko]

Is there a way to find out whether 7 is included in 1237654 or not in VBA without using Error handler?

It's really easy with a formula:

=ISNUMBER(FIND(7,1237654))

 

[prajul89]

Thank That's what I wanted. Thanks a ton!!

 

[prajul89]

If WorksheetFunction.IsNumber(WorksheetFunction.Find(7, 12345)) = True Then Exit Sub

Can you tell me Why doesn't this work??

 

[T. Valko]

If WorksheetFunction.IsNumber(WorksheetFunction.Find(7, 12345)) = True Then Exit Sub

Can you tell me Why doesn't this work??

I'm not much of a programmer so someone else will need to help you with this.

You may be better off using a native VBA test instead of using VBA to evaluate a worksheet formula.

 

[taurean]

You can test this in VBA as it is by putting Square Brackets around like:

If [IsNumber(Find(7, 12345))] = True Then Exit Sub

Or as Biff has suggested use VBA's built in InStr function can do this efficiently like:

If InStr(12345, 7) > 0 = True Then Exit Sub

 

[prajul89]

Cooooooooolll you always comes to rescue.

 

[taurean]

And if you want to implement your own code then you will have to add error handling.

Your arrangement will work fine when there's 7 in the other string.

But if it is not there then VBA raises an error. So revised code will look like this:

'Trapping the error that VBA will raise on not finding 7
On Error GoTo ContinueCode
If WorksheetFunction.IsNumber(WorksheetFunction.Find(7, 12345)) = True Then Exit Sub
ContinueCode:
'Resetting the error handling settings
On Error GoTo 0
MsgBox "Try this!"