Answered by:
Find Function in VBA
Question

Just trying to use the Find function, like in a spreadsheet but within VBA code
Here is the formula in my sheet: =LEFT(B5,(FIND("",B5)1))
It works fine but when I code this up in VBA it says the FIND function is not defined
Here is my line of code where the error occurs: Cells(R, C) = Left(Cells(R, C), Find("", Cells(R, C))  1))
Seems simple but I'm a newbie  thanks! Assuming I need to use the .Find method somehow
Answers

The following for equivalent in VBA
Cells(R, C) = Left(Cells(R, C), InStr(1, Cells(R, C), "")  1)
Regards, OssieMac
 Proposed as answer by L.HlModerator Friday, February 13, 2015 2:06 AM
 Marked as answer by L.HlModerator Sunday, February 15, 2015 1:37 PM

Because you said that you are learning, I decided to add a little more to my previous post.
You can use Find but you need to specify that it is a WorksheetFunction like the following.
Cells(R, C) = Left(Cells(R, C), WorksheetFunction.Find("", Cells(R, C))  1)
However, the Find function returns an error if the text being searched for is NOT found. The Instr function simply returns zero.
The next thing is an explanation of using the Instr function in conjunction with the Left function. If Instr does not find the text and returns zero as the argument for the Left function then zero characters are returned. However, in your formula you are subtracting 1 from zero giving you a negative number so Left function returns an error.
You should therefore handle the situation with code like the following to ensure that the text is found before applying the Left function.
Dim lngPos As Long 'It is good programming to dimension the variables.
lngPos = InStr(1, Cells(R, C), "")
If lngPos > 0 Then
Cells(R, C) = Left(Cells(R, C), lngPos  1)
End IfRegards, OssieMac
 Proposed as answer by L.HlModerator Friday, February 13, 2015 2:06 AM
 Marked as answer by L.HlModerator Sunday, February 15, 2015 1:37 PM
All replies

The following for equivalent in VBA
Cells(R, C) = Left(Cells(R, C), InStr(1, Cells(R, C), "")  1)
Regards, OssieMac
 Proposed as answer by L.HlModerator Friday, February 13, 2015 2:06 AM
 Marked as answer by L.HlModerator Sunday, February 15, 2015 1:37 PM

Because you said that you are learning, I decided to add a little more to my previous post.
You can use Find but you need to specify that it is a WorksheetFunction like the following.
Cells(R, C) = Left(Cells(R, C), WorksheetFunction.Find("", Cells(R, C))  1)
However, the Find function returns an error if the text being searched for is NOT found. The Instr function simply returns zero.
The next thing is an explanation of using the Instr function in conjunction with the Left function. If Instr does not find the text and returns zero as the argument for the Left function then zero characters are returned. However, in your formula you are subtracting 1 from zero giving you a negative number so Left function returns an error.
You should therefore handle the situation with code like the following to ensure that the text is found before applying the Left function.
Dim lngPos As Long 'It is good programming to dimension the variables.
lngPos = InStr(1, Cells(R, C), "")
If lngPos > 0 Then
Cells(R, C) = Left(Cells(R, C), lngPos  1)
End IfRegards, OssieMac
 Proposed as answer by L.HlModerator Friday, February 13, 2015 2:06 AM
 Marked as answer by L.HlModerator Sunday, February 15, 2015 1:37 PM
