Using WorksheetFunction.Find in VBA
 

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

Using WorksheetFunction.Find in VBA
 

Use Instr

Result = InStr(y, third) 0


--

HTH

Bob

"Schizoid Man" wrote in message
...
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

Using WorksheetFunction.Find in VBA
 

"Bob Phillips" wrote in message
...
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.

Using WorksheetFunction.Find in VBA
 

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.

--

HTH

Bob

"Schizoid Man" wrote in message
...
"Bob Phillips" wrote in message
...
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.