Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search String for Text (03)

    I don't see why this particular code does not return a result of "3".

    Sub Test()
    DimDim oSCriteria As String
    Dim af As WorksheetFunction

    oSCriteria = "601004"
    oCell = "xx601004 PTER-Employee Group Insurance"
    oResult = af.Search(oSCriteria, oCell) 'Should return a result of 3

    End Sub

    Thanks I must be overlooking the obvious,
    John

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

    Re: Search String for Text (03)

    DimDim is not valid, it should be Dim.

    You haven't declared oCell and oResult in this procedure.

    But the real problem: you have declared af, but not set it to anything. Add a line

    Set af = Application.WorksheetFunction

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search String for Text (03)

    Alternately to Hans' suggestion:
    instead of using the XL function Search, you can use INSTR and not use the AF variable at all:

    <pre>oResult = InStr(ocell, oSCriteria)</pre>


    Steve

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

    Re: Search String for Text (03)

    That is actually much more efficient. (I only pointed out the problem with John's code)

Posting Permissions

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