Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? | Excel Discussion (Misc queries) | |||
backwards find function to find character in a string of text | Excel Programming |