Search from one workbook to another

[dlo1503]

Hi

I have two workbooks open at the same time. Workbook 1 ("FAI TEST") is the user input form and Workbook 2 ("TEST OPEN ORDER BOOK TEST") is the master data file.

I am trying to write a code to search column A in Workbook 2 for a value match of Cell A2 in workbook 1. If it finds a match I would like to take the value from cell D66 on Workbook 1 and paste it into Column T of the row that the corresponding value is found in Workbook 2.

I cant upload my workbooks as they are data sensitive. I've attached the code I currently have but I am a modest beginner with VBA and am sure it is useless.

Hope someone can give me some help with this. Appreciate any pointers

NB. I Should also add. The VBA code needs to be located in Workbook 1 as Workbook 2 is a dynamic shared file that multiple users will be changing.




Sub UpdateW2()

Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Variant

Application.ScreenUpdating = False

Set w1 = Workbooks("FAI TEST.xlsm").Worksheets("FAI")
Set w2 = Workbooks("TEST OPEN ORDER BOOK TEST.xlsx").Worksheets("Supplier_FullOrderBook")

For Each c In w1.Range.Cells("a2")
FR = Application.Match(c, w2.Columns("A"), 0)
If IsNumeric(FR) Then c.Offset(, 19).Value = w1.Range("d66" & FR).Value
Next c

Application.ScreenUpdating = True


End Sub

 

[Alex Blakenburg]

You have a loop but your code is not using it and your requirement statement doesn't indicate it needs one.

If that is the case try the below.

    Dim w1 As Worksheet, w2 As Worksheet
    Dim FR As Long               ' XXX Changed FR to Long
    
    Application.ScreenUpdating = False
    
    Set w1 = Workbooks("FAI TEST.xlsm").Worksheets("FAI")
    Set w2 = Workbooks("TEST OPEN ORDER BOOK TEST.xlsx").Worksheets("Supplier_FullOrderBook")
    
    With Application
        FR = .IfError(.Match(w1.Range("A2"), w2.Columns("A"), 0), 0)
    End With
    
    If FR <> 0 Then
        w2.Range("T" & FR).Value = w1.Range("d66").Value
    End If
    
    Application.ScreenUpdating = True

End Sub

 

[dlo1503]

Thank you so much Alex! This works perfectly. Yes I had been adapting a different code so the loop wasn't necessary. Appreciate the speedy help

 

[Alex Blakenburg]

You're welcome. Glad I could help.