Help with vb Find
 

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.

Help with vb Find
 

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



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

Help with vb Find
 

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

--
Regards,
Tom Ogilvy

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