vlookup to pull info into a text box based on a combo box value in userform

[ollieb34]

Afternoon all,

I'm trying to pull a value from an adjacent cell within a table/range into a text box in a userform when the user selects an item from the combo box. See below code which I'm using that is returning a runtime error

Private Sub cmbProd_Change()

txtProd.Value = Application.VLookup(Me.cmbProd.Value, Sheets("Lookups").Range("C2:H17"), 2, 0)


End Sub

Any ideas as to what I could be doing wrong?

Thanks in advance

 

[Akuini]

Try:

    Private Sub cmbProd_Change()
    Dim x As Variant
    With Me.cmbProd
        If IsNumeric(.Value) And Not .Value = Empty Then
            x = Application.VLookup(CLng(.Value), Sheets("Lookups").Range("C2:H17"), 2, 0)
            If Not IsError(x) Then
                txtProd.Value = x
            Else
                MsgBox "NOT FOUND"
            End If
        End If
    End With

    End Sub

 

[ollieb34]

Try:

    Private Sub cmbProd_Change()
    Dim x As Variant
    With Me.cmbProd
        If IsNumeric(.Value) And Not .Value = Empty Then
            x = Application.VLookup(CLng(.Value), Sheets("Lookups").Range("C2:H17"), 2, 0)
            If Not IsError(x) Then
                txtProd.Value = x
            Else
                MsgBox "NOT FOUND"
            End If
        End If
    End With

    End Sub

Akuini,
You have saved me... Spot on and working perfectly...

Well done and thanks again

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.