Finding Matching Data in Separate Workbooks

[AussieJac]

Hi all,

I have the following macro that finds a contract number listed in Column C in one sheet to where it appears in the following 10 sheets (in column B) then it returns the message "Found in Data Base".

What I want is this to work with two separate workbooks - one containing a list of contract numbers to be found in the second workbook. And instead of returning "Found in Data Base" when a match is found, I would like it to copy the Name of the contract corresponding the the contract number as a check.

In the macro below I have opened up the second workbook but I am unsure how to tell the macro to switch between the two workbooks.

Sub FindMatch()
' Open Data Base file to facilitate matching process.
' Check for matching data

Dim currentfile As String
Dim WS As Worksheet
Dim R As Range

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Workbooks.Open Filename:= _
        "G:\Registration\Data List amended for DOD.xls"
    MsgBox ("Opening DataBase")
    
    Windows("Claimed Matching Data.xls").Activate
    Sheets("M - Matching Data").Select
    [L3].Select
    While ActiveCell.Offset(0, -9) <> Empty
       
        For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
            Set R = WS.Range("b2:b3000").Find(ActiveCell.Offset(0, -9))

            If Not R Is Nothing Then
                ActiveCell.Offset(0, 1).Value = "Found in Data Base"
                  
        Exit For
            End If
        Next
        ActiveCell.Offset(1).Select
        
    Wend
End Sub

Any help would be appreciated.
Jac

 

[AussieJac]

If you're there Macro Man.....

I have to go, its 5pm here in not so sunny Brisbane.
I'll check this site in the morning to see if you have had any brainstorms.

Have a good day and thanks for your help thus far!

 

[Macropheliac]

Okay, let's try this again.

The assumptions:

-Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C loaded with numbers.

-Workbook "Data List amended for DOD" / Worksheets "List A", List B, "List C", "List D", "List E", "List F" / Column B loaded with numbers.


This procedure should:

-Open Workbook "Data List amended for DOD"

-Search Worksheets "List A", List B, "List C", "List D", "List E", "List F" / Column B for cells matching cells in Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C

-Place the Value "Found in Database" in the cell next to any cell in Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C for which a match is found.

-Close Workbook "Data List amended for DOD"

Sub Macro2()
' Open Data Base file to facilitate matching process.
' Check for matching data

Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open Filename:= _
"G:\Registration\Data List amended for DOD.xls"

MsgBox ("Opening DataBase")

ThisWorkbook.Sheets("M - Matching Data").Activate

For Each Tcell In ActiveSheet.Range("C3:C" & ActiveSheet.Range("C65536").End(xlUp).Row)
        Set Myrange = Tcell.Offset(0, 1)
        Myvalue = Tcell.Value
        
        For Each WS In Workbooks("Data List amended for DOD.xls").Sheets
                Select Case WS.Name
                    Case "List A", "List B", "List C", "List D", "List E", "List F"
                        With WS.Range("B2:B3000")
                            Set R = .Find(Myvalue, LookIn:=xlValues)
                            If Not R Is Nothing Then
                                    Myrange.Value = "Found in Database"
                            End If
                        End With
                End Select
        Next WS
Next Tcell

Windows("Data List amended for DOD.xls").Close False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Let me know how it works out.

Mac

 

[AussieJac]

Thanks again for your help. I have updated the code with your revision but it errors at For Each Tcell In ActiveSheet.Range("c3:c" & ActiveSheet.Range("C65536").End(x1up).Row)

Sub test2()
' Open Data Base file to facilitate matching process.
' Check for matching data

Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Workbooks.Open Filename:= _
        "G:\Registration\Data List amended for DOD.xls"
    MsgBox ("Opening DataBase")
    
 ThisWorkbook.Sheets("M - Matching Data").Activate
 
For Each Tcell In ActiveSheet.Range("c3:c" & ActiveSheet.Range("C65536").End(x1up).Row)

    Set Myrange = Tcell.Offset(0, 1)
    Myvalue = Tcell.Value
    
        For Each WS In Workbooks("Data List amended for DOD.xls").Sheets
           Select Case WS.Name
              Case "List A", "List B", "List C", "List D", "List E", "List F"
                 With WS.Range("b2:b3000")
        
                     Set R = .Find(Myvalue, LookIn:=xlValues)
                     If Not R Is Nothing Then
                          Myrange.Value = "Found in Data Base"
                     End If
                 End With
           End Select
        Next WS
        Next Tcell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I'll play with it today, but as usual I would appreciate any help you can offer.
Thanks again
Jac

 

[Macropheliac]

I'm not getting this error. What is the Error Message?

 

[AussieJac]

Isn't it like 2am over there??? Don't you ever sleep??? A good thing too cause I need your help....

Good old "Runtime error 1004"

 

[Macropheliac]

No. It's only 8:30 p.m. (But, I don't sleep much anyway )

Well, how is the data arranged in column C? Are there any empty cells between used cells? Is the entire column dedicated to contract numbers?

 

[AussieJac]

Data in column C consists of alpha numeric contract numbers of varying lengths (eg P1345, 776594, etc). The first two lines are headings so the first contract number is in cell C3 to C15.

There are no empty cells between used cells.

From C3 down its all contract numbers!

 

[AussieJac]

Hold the phone MacroMan!!!!!!

It's working!!!! Bless your cotton socks!

Owing to complete frustration and knowing the thing SHOULD work, I copied your code into a new module and its works.

I guess when I updated my code with your revision I must have stuffed something up. Doh!

Many thanks, but I might have one more question. So don't goto sleep yet!

Jac

 

[Macropheliac]

Post away! I would be more than glad to help you.

 

[AussieJac]

What if as part of the same code I wanted to copy the contents of the cell next to the matched cell as a check!

In other works, at present when it finds a match it records "Found in Data Base" But I would like to have it also show the Name of the contract as well.

therefore this procedure should:

-Open Workbook "Data List amended for DOD"

-Search Worksheets "List A", List B, "List C", "List D", "List E", "List F" / Column B for cells matching cells in Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C

-Place the Value "Found in Database" in Column M in Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" for which a match is found. AND in Column N copy the contract name from Column E in Workbook "Data List amended for DOD"
-Close Workbook "Data List amended for DOD"

I know, clear as mud!