Re: find data in adjacent cell

Giganews Newsgroups
Subject: Re: find data in adjacent cell
Posted by:  Dave Peterson (peters…
Date: Sat, 27 May 2006

Whenever I've had to do stuff like this, there are always differences
(misspellings, extra spaces) that cause trouble.  But this may help you get
started with the bulk of them:

Option Explicit
Sub testme()

    Dim ListWks As Worksheet
    Dim RubWks As Worksheet
    Dim myCell As Range
    Dim FoundCell As Range

    Set ListWks = Worksheets("sheet1")
    Set RubWks = Worksheets("sheet2")

    With ListWks
        For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
            With myCell
                If Application.CountIf(RubWks.UsedRange, .Value) > 1 Then
                    .Offset(0, 1).Value = "Multiple matches"
                    Set FoundCell = RubWks.Cells.Find(what:=.Value, _
                                        after:=.Cells(1), lookat:=xlWhole, _
                                        searchorder:=xlByRows, _
                                        searchdirection:=xlNext, _
                    If FoundCell Is Nothing Then
                        .Offset(0, 1).Value = "No match"
                        .Offset(0, 1).Value = FoundCell.Offset(2, 0).Value
                    End If
                End If
            End With
        Next myCell
    End With
End Sub

Each time you run this macro, it plops something into cell to the right.

If you're new to macros, you may want to read David McRitchie's intro at:

Leon Jaeggi wrote:
> I have been given a excel sheet of 50+ columns 2000+ rows and all over the
> place are the some 2500 names and telephone numbers and lots of rubbish -
> fortunately  the telephone number that belongs to each name is 2 rows down in
> the same column. (On a row with other names and lots of rubbish) If you Look
> for X, Y, and H names and numbers below you can see what I mean.
> X-name  rubbish rubbish H-name
> rubbish Y-name  rubbish rubbish
> X-number        rubbish rubbish H-number
> rubbish Y-number        J-name
> I have a separate sheet of 400+ names I want the telephone numbers for. All
> of my 400 names and numbers are in the 50x2000 row sheet
> How can I search for X-name (out of my list) and put in the next column the
> number that relates to it from the 50x2000 sheet (two rows down in the same
> column)
> So I end up with;
> X-name  X-number
> Y-name  Y-number
> H-name  H-number
> Regards
> Leon


Dave Peterson



In response to

find data in adjacent cell posted by Leon Jaeggi on Sat, 27 May 2006