Help with vb Find

[james]

Hello,
I can not seem to figure out the FIND command in VB code. It works great
in the worksheet. What I have is several hundred cells that start off with 2
to 5 characters then a "." then 8 numbers followed by another "." 3 numbers
then 2 letters (ie.. ABCD.12345678.012AB). I need the information in the
middle, the 12345678 by it's self. I try to use the following code, but it
does not like the FIND portion (Sub or Function not defined error)

If MID(Cells(irow, 3), FIND(".", Cells(irow, 3)) + 1, 8) = "12345678" then
..... do this code ....
End If

The only way I have been able to get the results I need is with the
following:

For i = 2 to 6
x = MID(Cells(irow, 3), i, 1)
If x = Chr(46) then ' the "."
s = i
Exit For
End If
Next
If MID(Cells(irow, 3), s + 1, 8) = "12345678" then
..... do this code ....
End If

This works but I really did not want to search several times on each and
every cell on hundreds of lines.

Any help would be greatly appreicated.

James K.

 

[Bigwheel]

Hi James

To use a worksheet function in VBA it should be preceded by
"Application.WorksheetFunction"
i.e.

If MID(Cells(irow, 3), Application.WorksheetFunction.Find(".", Cells(irow,
3)) + 1, 8) = "12345678" then
..... do this code ....
End If

 

[Tom Ogilvy]

If MID(Cells(irow, 3), Instr(Cells(irow, 3), ".") + 1, 8) = "12345678" then
..... do this code ....
End If