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?

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?

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

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?

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?