Vlookup Syntax and user input issues

Question

I'm trying to create a macro that compares two user in-putted worksheets then moves the differences to different sheets depending on why its different.

The code first asks for input of the newest data and opens that sheet. Then it asks for the location of the older data to compare with but doesn't open it. It adds the necessary sheets to copy to.

It then goes down a column cell by cell looking for the matching serial on the second work book (this is mainly to ensure that its comparing the correct data in-case formatting is off). Once it finds the matching serial it compares the second serial for both entry's and depending on if its different or new input into one of the sheets.

The main issue I'm having is with VLookup. It is having multiple errors 424, 1004 and Compile expression errors. I need a little guidance as to why its having these issues. I have searched and found a lot on needing to have brackets to reference a file but when I follow those formats exactly it throws the expression error.

Any advice is appreciated.

Sub Compare()

'Open workbooks
''Worksheet 1

Dim filter As String
Dim caption As String
Dim WB1FN As String
Dim WB1 As Workbook

filter = "Excel Sheets (*.xlsx),*.xlsx"
caption = "Please select newest equipment file"
MsgBox (caption)
WB1FN = Application.GetOpenFilename(filter, , caption)

        If WB1FN = "False" Then
            MsgBox "File not selected to import"
            Exit Sub
        End If

Set WB1 = Application.Workbooks.Open(WB1FN)

''Worksheet 2

Dim caption2 As String
Dim WB2FN As String

filter = "Excel Sheets (*.xlsx),*.xlsx"
caption2 = "Please select previous equipment file"
MsgBox (caption2)
WB2FN = Application.GetOpenFilename(filter, , caption)

        If WB2FN = "False" Then
            MsgBox "File not selected to import"
            Exit Sub
        End If

'Comparing data
''MS find and compare

Dim MS1 As String
Dim ESN1 As String
Dim ESN2 As String
Dim LastRow As Long
Dim i As Integer
Dim d As Integer
Dim n As Integer
Dim Filename As String

d = 4
n = 4

Set WB1 = ActiveWorkbook

'Create sheets

Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "A"
Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "B"
Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "C"

'Gets the last row number

ActiveWorkbook.Sheets(1).Activate
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For i = 4 To LastRow

''Assigning MS1,ES1,ES2

    MS1 = Cells(i, 6)
    ESN1 = Cells(i, 15)
    ESN2 = Application.WorksheetFunction.VLookup(MS1, '[" & WB2FN & "]Sheet1'! [R3C6:R10000C15], 10, False)
''Compare ESN and copy data

        If ESN2 <> ESN1 Then
        cell.EntireRow.Copy Sheets(2).Cells(d, 1)
        n = d + 1
        ElseIf Application.WorksheetFunction.IsNA(ESN2) = "TRUE" Then
        cell.EntireRow.Copy Sheets(4).Cells(n, 1)
        n = n + 1
        End If
Next i

'X find and copy

Dim OEM As String

ActiveWorkbook.Sheets(2).Activate

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

n = 3
i = 3

For i = 3 To LastRow

''Check for X

    OEM = Cells(i, 4)

    If OEM = "x" Then
        cell.EntireRow.Copy Sheets(3).Cells(n, 1)
        n = n + 1
    End If

Next i

MsgBox "Compare successful"

End Sub

score 0 · Accepted Answer · edited May 23 '17 at 10:26

have brackets to reference a file You can only use that approach if you are assigning a formula to a cell or range.

Example:

Dim myformula As String

myformula = "=VLOOKUP(" & MS1 & _
    ",'[" & WB2FN & "]Sheet1'! [R3C6:R10000C15], 10, False)"
Range("A1").Formula = myformula

But if you use VBA Worksheet Function, you need to somehow access the database or table you are fetching data from at runtime. Meaning you have to pass objects on the arguments and not strings as you do in above.
Something like:

'~~> the rest of your code before Vlookup here

Dim wb As Workbook
Dim mytable As Range

Set wb = Workbooks.Open(WN2FN, , True) '~~> read only, avoid errors when file in use
Set mytable = wb.Sheets("Sheet1").Range("F3:O10000")

On Error Resume Next '~~> to handle when Vlookup returns #N/A or errors out
ESN2 = Application.WorksheetFunction.VLookup(MS1, mytable, 5, 0)
If Err.Number <> 0 Then myvalue = CVErr(xlErrNA)
On Error GoTo 0 '~~> reset error handling to trap other errors

Debug.Print ESN2

I just provided the part where you use the Vlookup WorksheetFunction. You can use the rest of your code before it. Basically above code:

assigns source table to variable and passed it directly to Vlookup arguments.
Uses Vlookup via VBA WorksheetFunction to fetch data.

Take note of the OERN (On Error Resume Next) routine and OEG0 (On Error Goto 0).
In VBA when a Worksheet Function returns an error (eg. #N/A for Vlookup), the code errors out and stops execution. There is no IFERROR like we have in worksheet formulas. So you need to handle it using error handling routines.

Also take note that it is better to fully qualify the objects you're working on.
This is a good place to start to optimize your codes and avoid runtime errors.

Vlookup Syntax and user input issues

1 Answers1