Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    String find within String (Excel 2003)

    Hi all,

    I have a string value that I would like to find within a string within a cell. I have been trying to use the worksheet function "FIND" but I can not get the property of the worksheetfunction class....what item in the reference Library do I need to check in order to use this function if this is the problem....

    Is the Find worksheetfunction the right function to use if I am to give a range as well?

    Thanks,
    Darryl.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: String find within String (Excel 2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I think you meant:
    <code>
    Dim intPos As Integer
    intPos = InStr(Range("A1"), "and")
    </code>
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: String find within String (Excel 2003)

    Yep, thanks. I'll correct it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: String find within String (Excel 2003)

    Error corrected by HansV - thanks, Rory!

    You could use something like this to find the position of the string "and" in the value of cell A1.

    Dim intPos As Integer
    intPos = Application.WorksheetFunction.Find("and",Range("A1 "))

    But you don't really need the worksheet function in code, since VBA has the InStr function. Type InStr in a module or in the Immediate window and press F1 to get help. Example:

    Dim intPos As Integer
    intPos = InStr(Range("A1"), "and")

    Note that the arguments are in the reverse order compared with the FIND function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •