Finding Matching Data in Separate Workbooks

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57
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.

Code:
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 :confused:
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
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:
Code:
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

Board Regular
Joined
Jul 7, 2005
Messages
57
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


Rich (BB code):
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 :confused:
 

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
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
 

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57

ADVERTISEMENT

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??? :cry:
 

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165

ADVERTISEMENT

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

Board Regular
Joined
Jul 7, 2005
Messages
57
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! :p

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 :biggrin:

Jac
 

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
Okay, try the following. If it isn't correct, atleast we have somewhere to begin.

Code:
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. :pray:
Mac
 

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57
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
 

Forum statistics

Threads
1,140,944
Messages
5,703,305
Members
421,290
Latest member
james90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top