Populate userform textbox based on vLookup value

[wpryan]

Hi All, I want to populate a userform based on a value based on a vLookup formula. The textbox is not being populated, and I can't figure out why... I'm getting an error

Unable to get the vLookup property of the WorksheetFunction class

Here's the code:

Private Sub cboSN_Change()
   
    Dim ws As Worksheet
    Dim sRange As Range
    Dim lRow As Long

    Set ws = Sheets("Lookup")
    lRow = ws.Cells(Rows.Count, "G").End(xlUp).Row
    Set sRange = ws.Range("G1:I" & lRow)

    Me.txtCustomer = Application.WorksheetFunction.VLookup(Me.cboSN, sRange, 3, False)
    Me.txtModel = Application.WorksheetFunction.VLookup(Me.cboSN, sRange, 2, False)

End Sub

...any ideas?

 

[DanteAmor]

I'm getting an error

The error is because the data to search does not exist. I recommend using the Find method.
This way you can control when the data does not exist:

Private Sub cboSN_Change()
  Dim ws As Worksheet
  Dim sRange As Range, f As Range
  Dim lRow As Long

  Set ws = Sheets("Lookup")
  lRow = ws.Cells(Rows.Count, "G").End(xlUp).Row
  Set sRange = ws.Range("G1:I" & lRow)
  
  'Use the find method to find the data.
  Set f = sRange.Find(Me.cboSN, , xlValues, xlWhole, , , False)
  
  If Not f Is Nothing Then
    Me.txtCustomer = f.Offset(0, 2).Value       'You can use offset to the right.
    Me.txtModel = ws.Range("H" & f.Row).Value   'Or you can directly use the cell.
  Else
    MsgBox "Does not exist"
  End If
End Sub

Here you can see the advantages of using the Find method.
(please use the subtitles in your language)

 

[wpryan]

The error is because the data to search does not exist. I recommend using the Find method.
This way you can control when the data does not exist:

Private Sub cboSN_Change()
  Dim ws As Worksheet
  Dim sRange As Range, f As Range
  Dim lRow As Long

  Set ws = Sheets("Lookup")
  lRow = ws.Cells(Rows.Count, "G").End(xlUp).Row
  Set sRange = ws.Range("G1:I" & lRow)
 
  'Use the find method to find the data.
  Set f = sRange.Find(Me.cboSN, , xlValues, xlWhole, , , False)
 
  If Not f Is Nothing Then
    Me.txtCustomer = f.Offset(0, 2).Value       'You can use offset to the right.
    Me.txtModel = ws.Range("H" & f.Row).Value   'Or you can directly use the cell.
  Else
    MsgBox "Does not exist"
  End If
End Sub

Here you can see the advantages of using the Find method.
(please use the subtitles in your language)

Thanks for your help. It's working fine, e a sua espanol esta muy entendible para mi, quien habla Portuguese como mia segunda idioma.

 

[smozgur]

Thanks for your help. It's working fine

The marked solution post has been changed accordingly.

@wpryan: In your future questions, please mark the post as the solution that answered your question instead of your feedback post. This way, it will be more helpful to future readers. No further action is required for this thread.