Need help with Vlookup in userform

[Ahmed Fawad]

I've got a Sales Invoice form, I want to take all details when I enter an old invoice number, I know it's very plain n simple but the error it's showing I'm not getting it resolved. Form n VBA code is there.

Same code I'm using on Vehicle reg. # to get all the details if already in the record, it's working perfectly.

The error is "Unable to get the VLookup property of the WorksheetFunction class"

If invoice # doesn't exist, it'll go for a new record, but if it does, it'll show the relative records in related fields.

Private Sub txtinv_AfterUpdate()

    
    With Me
    
        cmbsflock = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 2, 0)
        
        txtsdate = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 3, 0)
        
        txtvreg = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 4, 0)
        
        txtdname = Application.WorksheetFunction.VLookup(Me.txtvreg, Worksheets("SALES").Range("B:K"), 5, 0)
        
        txtmob = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 6, 0)
        
        txtpartyn = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 7, 0)
        
        txtdealern = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 8, 0)
        
        txt1w = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 9, 0)
        
        txt2w = Application.WorksheetFunction.VLookup(Me.txtinv, Worksheets("SALES").Range("B:K"), 10, 0)
        
    End With
    
End Sub

Please advise...

TIA

 

[Ahmed Fawad]

@DanteAmor
Sir, Can u help with this?

 

[DanteAmor]

Try the following
I really don't know in which column you have the invoice numbers.
Adjust columns A, B, C, etc.

Private Sub txtinv_AfterUpdate()
  Dim sh As Worksheet
  Dim f As Range
  
  Set sh = Sheets("SALES")
  Set f = sh.Range("A:A").Find(txtinv.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cmbsflock.Value = sh.Range("B" & f.Row).Value
    txtsdate.Value = sh.Range("C" & f.Row).Value
    '
    'continue with the other controls
  Else
    MsgBox "invoice # " & txtinv.Value & " doesn't exist"
    'it'll go for a new record
  End If
End Sub

 

[Ahmed Fawad]

Perfect, working like butterball
So kind of you,

Thanks a lot.


I did this to work it on for reference if anyone else finds this post useful.

Private Sub txtinv_AfterUpdate()
  Dim sh As Worksheet
  Dim f As Range
  
  Set sh = Sheets("SALES")
  Set f = sh.Range("B:B").Find(txtinv.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cmbsflock.Value = sh.Range("C" & f.Row).Value
    txtsdate.Value = sh.Range("D" & f.Row).Value
    txtvreg.Value = sh.Range("E" & f.Row).Value
    txtdname.Value = sh.Range("F" & f.Row).Value
    txtmob.Value = sh.Range("G" & f.Row).Value
    txtpartyn.Value = sh.Range("H" & f.Row).Value
    txtdealern.Value = sh.Range("I" & f.Row).Value
    txt1w.Value = sh.Range("J" & f.Row).Value
    txt2w.Value = sh.Range("H" & f.Row).Value
    'continue with the other controls
  Else
    MsgBox "invoice # " & txtinv.Value & " doesn't exist"
    'it'll go for a new record
  End If
End Sub