How to create a Excel VBA that uses vlookup variables

Question

[Search the given cell values in a worksheet to find corresponding info another workbook and returning it the original workbook in the corresponding first empty column]

Sub Macro1()   

Dim filename As String
Dim myFileName As Workbook
Dim mySheetName As Worksheet
Dim myRangeName As Range

'get workbook path
filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")

'set our workbook and open it
Set myFileName = Application.Workbooks.Open(filename)

'set our worksheet
Set mySheetName = myFileName.Worksheets("Table 1")

'set the range for vlookup all active rows and columns
Set myRangeName = mySheetName.Range("A1").CurrentRegion

'return to the original Workbook
ThisWorkbook.Activate


Dim LookUp As String
Dim returnValue As Variant
Dim OriginalCell As String
Dim UpdatedCell As String
Dim FirstRow As String

Set Rng = ActiveSheet.Cells

lastRow = Rng.Find(what:="*", after:=Rng.Cells(1), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

WhatToFind = Chr(10)

'Finds all the rows with sequnce numbers then deletes everything in the specified cell after the first line break

For i = 1 To lastRow

    FindRow = Range("A:A").Find(what:=i, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Row

    If FindRow >= 1 Then
        OriginalCell = Cells(FindRow, "B").Value
        UpdatedCell = Left(OriginalCell, InStr(OriginalCell, WhatToFind) - 1)
        Cells(FindRow, "B").Value = UpdatedCell

        ' Uses the new cleaned up specified cell and searches another workbook,
        ' which the user selects and the first work sheet within that workbook and returns
        ' the corresponding info back to the original workbook in a the the next empty column. 

        LookUp = Application.WorksheetFunction.VLookup(Cells(FindRow, 2), myRangeName, 1, False)

        Cells(i, "I").Value = LookUp
    End If
Next i

End Sub

Answer 1

I am not sure why you have the VLookup line set up like this:

LookUp = Application.WorksheetFunction.VLOOKUP(Cells(i, 2),"[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",1,False)

You alreay did 95% of the work defining the Range, you can use the VLookup line (and the previous line) like this:

    'set the range for vlookup all active rows and columns
    Set myRangeName = mySheetName.Range("A1").CurrentRegion

    ' Uses the new cleaned up specified cell and searches another workbook which the user selects and the first worksheet
    ' within that workbook and returns the corresponding info back to the original workbook in a cell next to the empty column.
    LookUp = Application.WorksheetFunction.VLookup(Cells(i, 2), myRangeName, 1, False)

Edit 1: added the code to support the additional request by PO.

Sub Macro1()

Dim filename                As String
Dim myFileName              As Workbook
Dim currentSheet            As Worksheet
Dim mySheetName             As Worksheet
Dim myRangeName             As Range
Dim lastRow                 As Long
Dim i                       As Long
Dim matchRow                As Long


'set current worksheet
Set currentSheet = ThisWorkbook.Worksheets("Table 1")

'get workbook path
filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")

'set our workbook and open it
Set myFileName = Application.Workbooks.Open(filename)

'set searched worksheet
Set mySheetName = myFileName.Worksheets("Table 1")

' find last row in Column A ("Item No.")
lastRow = mySheetName.Cells(mySheetName.Rows.Count, "A").End(xlUp).Row

'set the range for Vlookup all active rows and columns
Set myRangeName = mySheetName.Range("A1:A" & lastRow)

' find last row in Column B in This Workbook ("Item No.")
lastRow = currentSheet.Cells(currentSheet.Rows.Count, "B").End(xlUp).Row

For i = 2 To lastRow
    With currentSheet
        If Not IsError(Application.Match(.Cells(i, "B"), myRangeName, 0)) Then
            matchRow = Application.Match(.Cells(i, "B"), myRangeName, 0)
            .Cells(i, "J") = mySheetName.Cells(matchRow, "J").Value
            .Cells(i, "K") = mySheetName.Cells(matchRow, "Q").Value
        Else ' Item No. record not found
            ' put #NA in cells, to know it's not found
            .Cells(i, "J") = CVErr(xlErrNA)
            .Cells(i, "K") = CVErr(xlErrNA)
        End If

    End With
Next i

End Sub

Answer 2

There is a syntax error - change

& myRangeName & ",1,False)

to

& myRangeName ,1,False)