Search from one workbook to another

dlo1503

New Member
Joined
Feb 24, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

VBA Code:
    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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top