If IsError Returning True, but not executing If instructions-CodePudding

I'm evaluating this line for an error (when it doesn't find a match), then getting the info I need if no match is found. It is returning a value of true when I hover over the " = True" Statement as I step through the code, but it just throws the error 1004 instead of going to the "If true" series of input boxes.

If IsError(Target.Offset(0, 1) = Application.WorksheetFunction.XLookup(WorksheetFunction.Text(Target, "General"), Sheets("tblItem").Columns(2), Sheets("tblItem").Columns(3))) = True Then
            Target.Offset(0, 1) = InputBox("Description?")
            Target.Offset(0, 2) = InputBox("Project Number?")
            Target.Offset(0, 3) = InputBox("Brand?")
            Target.Offset(0, 4) = InputBox("Brand Item Number?")
            Target.Offset(0, 5) = InputBox("Unit Cost?")
            Target.Offset(0, 6) = InputBox("Unit Weight?")
            Target.Offset(0, 7) = InputBox("Weight's UoM?")
        Else
            Target.Offset(0, 1) = Application.WorksheetFunction.XLookup(WorksheetFunction.Text(Target, "General"), Sheets("tblItem").Columns(2), Sheets("tblItem").Columns(3))
        End If

CodePudding user response：

You can't use IsError() to trap a run-time error like that:

Dim res

'no `WorksheetFunction`
res = Application.XLookup(Target.Value, Sheets("tblItem").Columns(2), _
                                        Sheets("tblItem").Columns(3))

If IsError(res) Then
    Target.Offset(0, 1) = InputBox("Description?")
    Target.Offset(0, 2) = InputBox("Project Number?")
    Target.Offset(0, 3) = InputBox("Brand?")
    Target.Offset(0, 4) = InputBox("Brand Item Number?")
    Target.Offset(0, 5) = InputBox("Unit Cost?")
    Target.Offset(0, 6) = InputBox("Unit Weight?")
    Target.Offset(0, 7) = InputBox("Weight's UoM?")
Else
    Target.Offset(0, 1) = res
End If

Note if you drop the WorksheetFunction you can skip the run-time error in the event of no match being found, and instead test the return value to check what happened.