Синтаксис Vlookup и проблемы с пользовательским вводом

Question

Я пытаюсь создать макрос, который сравнивает две введенные пользователем рабочие таблицы, а затем перемещает различия на разные таблицы в зависимости от того, почему они разные.

Код сначала запрашивает ввод самых новых данных и открывает этот лист. Затем он запрашивает расположение более старых данных для сравнения, но не открывает их. Он добавляет необходимые листы для копирования.

Затем он перемещается по ячейке столбца за ячейкой в ​​поисках совпадающего серийного номера во второй рабочей книге (это главным образом для того, чтобы гарантировать, что его сравнение правильного форматирования данных в случае отключено). Как только он находит соответствующий серийный номер, он сравнивает второй серийный номер для обеих записей и в зависимости от того, отличается ли он от нового или нового ввода на одном из листов.

Основная проблема, с которой я столкнулся, связана с VLookup. Имеет множественные ошибки 424, 1004 и ошибки выражения компиляции. Мне нужно небольшое руководство относительно того, почему это имеет эти проблемы. Я много раз искал и обнаруживал необходимость иметь скобки для ссылки на файл, но когда я точно следую этим форматам, он выдает ошибку выражения.

Любой совет приветствуется.

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

Answer 1

have brackets to reference a file Вы можете использовать этот подход, только если вы назначаете формулу для ячейки или диапазона.

Пример:

Dim myformula As String

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

Но если вы используете функцию рабочего листа VBA, вам нужно каким-то образом получить доступ к базе данных или таблице, из которой вы извлекаете данные во время выполнения. Это означает, что вы должны передавать объекты в аргументах, а не в строках, как вы делали выше.
Что-то вроде:

'~~> 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

Я только что предоставил ту часть, где вы используете функцию Vlookup WorksheetFunction. Вы можете использовать оставшуюся часть кода перед этим. В основном код выше:

• присваивает исходную таблицу переменной и передает ее непосредственно аргументам Vlookup.
• Использует Vlookup через VBA Worksheet Function для получения данных.

Обратите внимание на процедуру OERN (On Error Resume Next) и OEG0 (On Error Goto 0).
В VBA, когда функция рабочего листа возвращает ошибку (например, #N/A для Vlookup), код выдает ошибку и останавливает выполнение. Там нет IFERROR, как у нас в формулах листа. Таким образом, вы должны обработать это с помощью процедур обработки ошибок.

Также обратите внимание, что лучше полностью квалифицировать объекты, над которыми вы работаете.
Это хорошее место для начала оптимизации ваших кодов и предотвращения ошибок во время выполнения.