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]

It means no value is found, so you need to trap the error.
Try:

Private Sub cmbProd_Change()
Dim x As Variant
x = Application.VLookup(Me.cmbProd.Value, Sheets("Lookups").Range("C2:H17"), 2, 0)

If Not IsError(x) Then
    txtProd.Value = x
Else
    MsgBox "NOT FOUND"
End If

End Sub

 

[ollieb34]

It means no value is found, so you need to trap the error.
Try:

Private Sub cmbProd_Change()
Dim x As Variant
x = Application.VLookup(Me.cmbProd.Value, Sheets("Lookups").Range("C2:H17"), 2, 0)

If Not IsError(x) Then
    txtProd.Value = x
Else
    MsgBox "NOT FOUND"
End If

End Sub

Morning Akuni,
Thanks for the reply. tried the following code and every value which is selected from the combo box is now showing a msgbox "not found"

Does it matter that the data held within that combo box is a 6 digit number?

thanks

 

[Akuini]

Does it matter that the data held within that combo box is a 6 digit number?

Sorry for the late reply.
Try converting combobox value to Long type:

    Private Sub cmbProd_Change()
    Dim x As Variant
    x = Application.VLookup(CLng(Me.cmbProd.Value), Sheets("Lookups").Range("C2:H17"), 2, 0)

    If Not IsError(x) Then
        txtProd.Value = x
    Else
        MsgBox "NOT FOUND"
    End If

    End Sub

 

[ollieb34]

Sorry for the late reply.
Try converting combobox value to Long type:

    Private Sub cmbProd_Change()
    Dim x As Variant
    x = Application.VLookup(CLng(Me.cmbProd.Value), Sheets("Lookups").Range("C2:H17"), 2, 0)

    If Not IsError(x) Then
        txtProd.Value = x
    Else
        MsgBox "NOT FOUND"
    End If

    End Sub

Morning Akuini,

No Problem, Yup thats the one... working fine now. Thank you very much for your support with this issue

 

[ollieb34]

Morning Akuini,

No Problem, Yup thats the one... working fine now. Thank you very much for your support with this issue

Akuini,
Working fine to pull the data, however upon resetting the form either using the reset button or the save button now throwing a Runtime Error 13- Type mismatch

any ideas?

thanks

 

[Akuini]

however upon resetting the form either using the reset button or the save button now throwing a Runtime Error 13- Type mismatch

I think it's a different a problem, so you need to start a new thread.

 

[ollieb34]

I think it's a different a problem, so you need to start a new thread.

the debug code links to the code you provided?

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

 

[Akuini]

What do you mean by:
resetting the form either using the reset button or the save button

Do you put this line in other than Private Sub cmbProd_Change?
x = Application.VLookup(CLng(Me.cmbProd.Value), Sheets("Lookups").Range("C2:H17"), 2, 0)

 

[ollieb34]

What do you mean by:
resetting the form either using the reset button or the save button

Do you put this line in other than Private Sub cmbProd_Change?
x = Application.VLookup(CLng(Me.cmbProd.Value), Sheets("Lookups").Range("C2:H17"), 2, 0)

When I save or reset the form it calls Sub Reset- which basically clears all the text boxes and fills the comboboxes ready.

iv also tried adding code to clear txtProd in sub reset- hoping that it would get round that error.

the above line of code you provided is only entered into CmbProd change