Unable to get the Find property of the WorksheetFunction Cla

[ronenbitman]

Hi,

i have an error Runtime Error:
Runtime Error 1004:
Unable to get the Find property of the WorksheetFunction Class

what happens is:

NameNumText = Cells(iRow, PaperNumCol)
iSpacePosition = WorksheetFunction.Find(" ", NameNumText)
Cells(iRow, PaperNameCol) = Mid(NameNumText, 1, iSpacePosition - 1)
Cells(iRow, PaperNumCol) = Mid(NameNumText, iSpacePosition + 1, Len _(NameNumText) - iSpacePosition)

these are the three lines what they should do is cell A has the value:
"111 ABCD"
i am giving iSpacePosition the Find of space withing cell A (answer is 4)
then Cell A gets "111"
and cell B gets "ABCD"
as expected.
but if Cell A contains "111" then i want excel to result in "111"
what happens is when Find can not find the Sub string it returns "#Value"
and then I get the error, how can i handle the #Value error so it will work?

10x 4 the help

 

[Juan Pablo González]

Re: Unable to get the Find property of the WorksheetFunction

Use InStr() instead to look for the space, it will return 0 if its not found.

 

[ronenbitman]

Re: Unable to get the Find property of the WorksheetFunction

10x for the quick help