none
Find Function in VBA RRS feed

  • 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

    Saturday, February 7, 2015 7:31 PM

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
    Saturday, February 7, 2015 9:05 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 If


    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
    Saturday, February 7, 2015 11:06 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
    Saturday, February 7, 2015 9:05 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 If


    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
    Saturday, February 7, 2015 11:06 PM
  • Thanks OssieMac. I really appreciate your detailed help! I'll try it out.
    Monday, February 9, 2015 6:27 AM