String Question

  • Thread starter Thread starter Ray Batig
  • Start date Start date
R

Ray Batig

I have a row of data named MyData. The cells contain names as strings, I
want to do an action if the string "Tank" is found in the cell. I wrote the
following code to locate the string within the cell name string (tszName):

TFPos = Application.WorksheetFunction.Find("Tank", tszName,1)

If TFPos > 0 Then
Do the action

This code works fine if Tank is in the string. If it is not there, the Find
returns #VALUE!, this stops the macro. I have looked at the other functions
that Excel provides, however all provide some sort of error code if the
string is not found.

Can some one suggest a workaround or a better technique?

Thanks in advance for your help!

Ray
 
I have a row of data named MyData. The cells contain names as strings, I
want to do an action if the string "Tank" is found in the cell. I wrote the
following code to locate the string within the cell name string (tszName):

TFPos = Application.WorksheetFunction.Find("Tank", tszName,1)

If TFPos > 0 Then
Do the action

This code works fine if Tank is in the string. If it is not there, the Find
returns #VALUE!, this stops the macro. I have looked at the other functions
that Excel provides, however all provide some sort of error code if the
string is not found.

Can some one suggest a workaround or a better technique?

Thanks in advance for your help!

Ray
Click to expand...


I would probably use the INSTR function.

But if you want to use FIND, if you use Application.Find(...) instead of
Application.WorksheetFunction.Find(...) then you will generate an error code
withing the routine that you can test and handle with the error handling
routines.


--ron
 
You can also do something like

Dim rng As Range
Set rng = Rows(1).Find("Tank")
If rng Is Nothing Then...
Else...
End If

HTH,
Shockley
 
Back
Top