Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Find command
I'm trying to use "Find" to determine the exact location of a space in a
string. (e.g. Smith Bob space is located in postion 6) In code I'm entering ActiveCell.Formula = "=Find(" ",D3,1)" to determine the space location. It errors every time. I believe it has to do with the quotation marks. This works fine as a formula in the spreadsheet, but in VBA an error occurs. Any ideas on why I get an error? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Find command
Use this and it works
ActiveCell.Formula = "=Find("" "",d3,1)" or ActiveCell = Application.WorksheetFunction.Find(" ", Range("D3"), 1) if you only want to write the data and not the formula (saves memory) Greetz "Stever" schreef: I'm trying to use "Find" to determine the exact location of a space in a string. (e.g. Smith Bob space is located in postion 6) In code I'm entering ActiveCell.Formula = "=Find(" ",D3,1)" to determine the space location. It errors every time. I believe it has to do with the quotation marks. This works fine as a formula in the spreadsheet, but in VBA an error occurs. Any ideas on why I get an error? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Find command
VBA has its own version to find things in a string. Take a look at InStr() in
VBA's help. Stever wrote: I'm trying to use "Find" to determine the exact location of a space in a string. (e.g. Smith Bob space is located in postion 6) In code I'm entering ActiveCell.Formula = "=Find(" ",D3,1)" to determine the space location. It errors every time. I believe it has to do with the quotation marks. This works fine as a formula in the spreadsheet, but in VBA an error occurs. Any ideas on why I get an error? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Find command
Find in VBA is equivalent to Ctrl + F or Edit - Find. You want to use
InStr(1, D3, " ") There is also InstrRev to find the last instance of a string within a string. (Did not exist in Excel 97) HTH Jim Thomlinson "Stever" wrote: I'm trying to use "Find" to determine the exact location of a space in a string. (e.g. Smith Bob space is located in postion 6) In code I'm entering ActiveCell.Formula = "=Find(" ",D3,1)" to determine the space location. It errors every time. I believe it has to do with the quotation marks. This works fine as a formula in the spreadsheet, but in VBA an error occurs. Any ideas on why I get an error? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Find command
To be exact you need to have a string value as the second argument.
InStr(1, activesheet.range("D3").value, " ") HTH Jim Thomlinson "Jim Thomlinson" wrote: Find in VBA is equivalent to Ctrl + F or Edit - Find. You want to use InStr(1, D3, " ") There is also InstrRev to find the last instance of a string within a string. (Did not exist in Excel 97) HTH Jim Thomlinson "Stever" wrote: I'm trying to use "Find" to determine the exact location of a space in a string. (e.g. Smith Bob space is located in postion 6) In code I'm entering ActiveCell.Formula = "=Find(" ",D3,1)" to determine the space location. It errors every time. I believe it has to do with the quotation marks. This works fine as a formula in the spreadsheet, but in VBA an error occurs. Any ideas on why I get an error? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND command | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Find Command | Excel Programming | |||
Find Command | Excel Programming | |||
Find Command | Excel Programming |