VBA Macro to find matching value in another workbook

[SnareStudios]

I am attempting to write a macro that lets a user select a cell containing a unique value, run the macro which prompts the user to select a workbook to "search" for that value in and return the row number of where that value was found to the original workbook.

Example:
- Highlight a cell with "ABC123" in Workbook1
- Run the macro
- Select a separate workbook (Workbook2)
- The macro searches that selected Workbook2 for "ABC123"
- If found it returns the row number where this unique "ABC123" value was found in Workbook2 to the ORIGINAL Workbook1.

Any help would be GREATLY appreciated.

 

[SnareStudios]

Does my explanation make sense?

 

[Domenic]

A couple of notes...

1. You didn't mention whether each sheet within the selected workbook needs to be searched, so I've assumed that each sheet needs to be searched.
   
2. Also, you didn't mention where you'd like to return the row number, so I've assumed that you'd like it returned one cell to the right of the selected cell.

Try...

Option Explicit

Sub test()

    Dim wkbOpen As Workbook
    Dim wks As Worksheet
    Dim Cell As Range
    Dim FoundCell As Range
    Dim Wkb As Variant

    On Error Resume Next
    Set Cell = Application.InputBox( _
        Prompt:="Please a cell containing the desired value.", _
        Title:="Select a Cell", _
        Type:=8)
    On Error GoTo 0
    
    If Cell Is Nothing Then Exit Sub
    
    Wkb = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xlsx), *.xlsx", _ [COLOR="SeaGreen"]'Change the file extension, accordingly[/COLOR]
        FilterIndex:=1, _
        Title:="Select a Workbook", _
        MultiSelect:=False)
        
    If Wkb = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set wkbOpen = Workbooks.Open(Filename:=Wkb)
        
    With wkbOpen
        For Each wks In wkbOpen.Worksheets
            Set FoundCell = wks.Cells.Find(what:=Cell.Value, _
                LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) [COLOR="SeaGreen"]'Adjust the parameters, accordingly[/COLOR]
            If Not FoundCell Is Nothing Then
                Cell.Offset(, 1).Value = FoundCell.Row
                .Close savechanges:=False
                Exit Sub
            End If
        Next wks
        .Close savechanges:=False
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Lookup value not found..."
    
End Sub

 

[SnareStudios]

The macro will only search Sheet1 (or whatever the first sheet is named) of the chosen workbook. It will only need to search that first sheet.

I am just wanting the row number assigned to a variable for later use. It doesn't need to be set to a cell value. Eventually it will be set to a cell formula.

 

[SnareStudios]

I was also wanting it to loop through a selection of cells (not just a single cell). The selection will be all within a single column.

 

[Domenic]

You say that eventually the row number will be used in a formula. How will it be used?

 

[SnareStudios]

I have a column (Column B) of serial numbers in a reporting workbook (Workbook1). I will select a range (lets say the first 5). I am then going to run the macro which will prompt for me to open another worksheet containing more detailed information for each SN in the selection (Workbook2).

It will then loop through the selection in Workbook1 and find the corresponding row number for each matching cell value in Workbook2.

It will then link Column E + row of that selected cell in Workbook1 to Column E + Row Value of where it found the match in Workbook2. Just referencing that cell as a formula. Example: ='C:\Example\[Workbook2.xlsx]Sheet1'!$E$8

 

[Domenic]

Try...

Option Explicit

Sub test()

    Dim FileName As Variant
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim UserRange As Range
    Dim Cell As Range
    Dim FoundCell As Range
    Dim FP As String
    Dim FN As String
    Dim SN As String
    Dim RowNum As Long
    
    Set UserRange = Application.Selection

    Set wksDest = ActiveWorkbook.ActiveSheet
    
    '//Change the file extension for the file filter, accordingly
    FileName = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xlsx), *.xlsx", _
        FilterIndex:=1, _
        Title:="Select a Workbook")
        
    If FileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set wkbSource = Workbooks.Open(FileName:=FileName)
    Set wksSource = wkbSource.Worksheets(1)
    
    FP = wkbSource.Path
    FN = wkbSource.Name
    SN = wksSource.Name
    
    For Each Cell In UserRange
        '//Change the parameters for Find, accordingly
        Set FoundCell = wksSource.Cells.Find(what:=Cell.Value, _
                LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
        If Not FoundCell Is Nothing Then
            RowNum = FoundCell.Row
            wksDest.Cells(Cell.Row, "E").Formula = "='" & FP & _
                Application.PathSeparator & "[" & FN & "]" & SN & "'!$E$" & RowNum
        End If
    Next Cell
    
    wkbSource.Close Savechanges:=False
    
    Application.ScreenUpdating = True
    
End Sub