Error 1004 Calling VLOOKUP from VBA

[MuddyBrain3]

I'm trying to call VLOOKUP from a VBA sub to return a Variant, n, from another worksheet. I've twisted and turned it any number of ways, but no luck. This is the latest coding:

n = Application.WorksheetFunction.VLookup("$H$9", Worksheets("Sales Record").Range("A1:ZZ1000"), counter , False)

Hope someone can give the correct syntax. Thanks.

 

[ChrisOswald]

Hi,

The 1004 error is (probably) being caused by the search value not being found. In an actual worksheet formula this returns a #N/A error, in VBA it raises a 1004 error. Something like this:

Sub V()
    Dim n
    On Error GoTo IGotNothing
    n = Application.WorksheetFunction.VLookup(Range("H9"), Worksheets("Sheet1").Range("A1:B4"), 2, False)
    On Error GoTo 0
    msgbox(V)
 
 
    Exit Sub 
    IGotNothing:
    If Err.Number = 1004 Then
        n = CVErr(xlErrNA)
        Resume Next
    Else
        MsgBox ("Broke it good, didn't you?")
        End
    End If
End Sub

should do the trick.

 

[MuddyBrain3]

Thanks Chris. I also posted in another forum and have plenty to consider! I'll need time to ponder all the information. Appreciate the suggestion. Muddy Brain is where I am right now so have to leave this for a while...