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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,043
Office Version
  1. 365
Platform
  1. Windows
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
 

dlo1503

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

Forum statistics

Threads
1,175,814
Messages
5,899,633
Members
434,791
Latest member
saham

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