Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find a string (2000)

    I have the following:


    <table border=1><td></td><td>A</td><td>B</td><td>C</td><td>1</td><td>Woodys House</td><td>...</td><td>Woody</td><td>2</td><td>Wimbledon Common</td><td>...</td><td>Common</td><td>3</td><td>Acacia Avenue</td><td>...</td><td>Acacia</td></table>

    I want to start in cell cell B1, pick the value up in C1 and see if it appears in the string in A1. If it does a number 1 appears, if not 0 in B1.

    This will continue down the list.

    I started using this:
    <div style="width: 100%; background-color: #FFFFFF;">Sub Match()

    Dim strLeft As String
    Dim strRight As String
    Dim rngCell As Range
    Range("B1").Select
    Set rngCell = ActiveCell

    strRight = rngCell.Offset(0, 1)
    strLeft = rngCell.Offset(0, -1)


    Cells.Find(What:=strRight, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate


    End Sub</div hiblock>

    But can see I am going no where, can you help
    Jerry

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

    Re: Find a string (2000)

    You don't really need code for this. Enter the following formula in B1:

    =IF(ISERROR(FIND(C1,A1)),0,1)

    and fill down as far as needed.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find a string (2000)

    Thanks for that, I have never seen it done that way. One problem though, this will only work if the case matches, ie it will return 0 if there is say Wimbledon in A1 but wimbledon in C2. Anyway to improve it...I have been given rather dirty data <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Jerry

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

    Re: Find a string (2000)

    Replace FIND with SEARCH. The FIND function is case sensitive, SEARCH isn't.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find a string (2000)

    Try:

    =IF(ISERROR(FIND(UPPER(C1),UPPER(A1))),0,1)
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find a string (2000)

    Outstanding...thank you <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    Jerry

Posting Permissions

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