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

 

[Macropheliac]

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.

Use the lines ThisWorkbook and Activeworkbook. Remember that ThisWorkbook always refers to the workbook inwhich the macro is stored. ActiveWorkbook refers, just as it states, to whatever workbook is active at the time the line is called.

Example:

Workbooks("Dummy").Open
ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = "100"
ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "200"
ThisWorkbook.Activate

I hope this helps.

Mac

 

[AussieJac]

Thanks so much for the reply.
I've been playing with it but still can't make it work. There is obviously some protocal as to where you insert "ActiveWorkbook" or "Thisworkbook"

I have highlighted in bold the lines I have added to the macro below.

Just to clarify;
File 1 "Claimed Matching Data.xls" - contains list of contract numbers in Column C

File 2 "Data List amended for DOD.xls" - contains archive listing of contract details over serveral sheets (eg List A, List B, etc) contract number to be matched is in Column B

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


    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
        Windows("Data List amended for DOD.xls").Activate        For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
            Set R = WS.Range("b2:b3000").ThisWorkbook.Sheets("M - Matching Data").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

Thanks again

 

[Macropheliac]

Can you tell me what this part of the code is supposed to do?

For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
            Set R = WS.Range("b2:b3000")
            ThisWorkbook.Sheets("M - Matching Data").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

 

[AussieJac]

Can you tell me what this part of the code is supposed to do?
Code:
For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
Set R = WS.Range("b2:b3000")
ThisWorkbook.Sheets("M - Matching Data").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

For each worksheet in file "Data List amended for DOD.xls" in array ("List A","List B","List C" etc) in Column B
Match contract number found in Column C in file "Claimed Matching Data.xls", sheet "M - Matching Data".


Clear as mud.... right???

 

[Macropheliac]

Clear as mud! I'm working on it. :wink:

 

[Macropheliac]

It looks like you're searching Column B in worksheets A-F in "Data List amended for DOD" to find the value in range "C3" of worksheet "M-Matching Data in workbook "Claimed Matching Data". Am I correct, so far?

If I am correct, is it possible to have more than one occurrence of the value?

 

[AussieJac]

You're pretty much correct!

It looks like you're searching Column B in worksheets A-F in "Data List amended for DOD"

Absolutly right!

to find the value in range "C3" of worksheet "M-Matching Data in workbook "Claimed Matching Data". ?

I want to find the value in Column C (refer code: Find.(Activecell.offset(0,-9)) of worksheet "M-Matching Data" in workbook "Claimed Matching Data"

If I am correct, is it possible to have more than one occurrence of the value?

No. The value will only appear once!


Many thanks for your efforts

Jac

 

[Macropheliac]

Okay, try the following. If it isn't correct, atleast we have somewhere to begin.

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

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

Set Myrange = ActiveCell

If ActiveCell.Offset(0, -9) <> Empty Then
        Myvalue = ActiveCell.Offset(0, -9).Value
        
        Windows("Data List amended for DOD.xls").Activate
        For Each WS In Sheets(Array("List A", "List B", "List C", "List D", "List E", "List F"))
        
                With WS.Range("b2:b3000")
                        Set R = .Find(Myvalue, LookIn:=xlValues, lookat:=xlWhole)
                End With
                
                If Not R Is Nothing Then
                        Myrange.Offset(0, 1).Value = "Found in Data Base"
                        Exit For
                Else
                        MsgBox "No Match Found"
                End If
        Next WS
Else
        MsgBox ("Cell is empty")
End If

ActiveCell.Offset(1).Select

Windows("Data List amended for DOD.xls").Close False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I hope it works. ray:
Mac

 

[AussieJac]

Thank you Macro-Man !
I have updated my code with your revision and the macro runs but it skips straight to the Msgbox "No Match Found"
But then records "Found in Data Base" CORRECTLY in cell M3 for the first contract number found.
I know that the following 3 contract numbers should also be found, so therefore something is wrong.

Thank you for trying. Have you got anything else in your bag of tricks??
Here's hoping...
Jac