Using WorksheetFunction.Find in VBA

[Schizoid Man]

Hi,

I have the following code snippet that I'm using to find whether one digit
is part of another large number using something like:

If IsNumeric(WorksheetFunction.Find(Third, y)) Then
Result = TRUE
Else
Result = FALSE
End If

where y is the large number and 'Third' represents the a particular digit.
If the expression in the IF statement does contain the said digit, the
result of the statement is that Result variable is assigned the value TRUE.
However, if the digit is not contained, then rather than hitting the ELSE
bit of the above statement, the code just unexpectedly terminates.

Any suggestions?

Thx,
Schiz

 

[Bob Phillips]

Use Instr

Result = InStr(y, third) > 0

 

[Schizoid Man]

Use Instr

Result = InStr(y, third) > 0


--

HTH

Bob

Hi Bob,

That certainly does help - your solution worked like a charm, though I'm not
sure why. How is it different from what I had originally written down?

Thx.

 

[Bob Phillips]

It is different because the worksheet function errors out if the value is
not found, and you are not handling an error. It can be done, like so

Dim tmp As Long

On Error Resume Next
tmp = WorksheetFunction.Find(third, y)
On Error GoTo 0

Result = tmp <> 0

but it is far more efficient to use a VBA function in VBA, rather than
calling out to Excel.