Run-time error 1004

[gleamng]

Good morning everyone
i have been having a running battle with Run-time error 1004 on this code, while i had tried many online solutions, yet i am stuck with this error.
Summarily, after trying the vlookup function, i also tried index & match function and get the same error code, so i was thinking may worksheet function dont want to work for my pet project, please kindly help this novice out. below is my vba code and thank you for your support & help.

Private Sub cmbItemID_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ProductMaster")

If Me.cmbItemID.Value = "" Then Me.txtRate.Value = ""

Else
    Me.txtRate.Value = Application.WorksheetFunction.VLookup(Me.cmbItemID.Value, sh.Range("B:F"), 5, False)
End If

End Sub

 

[steve the fish]

Tell us what is in cmbItemID. Ill guess a number. Make sure you are looking up the same thing. A textual numberfor example will not be found in a list of real numbers by vlookup.

 

[gleamng]

Tell us what is in cmbItemID. Ill guess a number. Make sure you are looking up the same thing. A textual numberfor example will not be found in a list of real numbers by vlookup.

Yes cmbItemID is a number.
Thanks

 

[Fluff]

How about

Private Sub cmbItemID_Change()
Dim sh As Worksheet
Dim x As Variant
Set sh = ThisWorkbook.Sheets("ProductMaster")

If Me.cmbItemID.Value = "" Then Me.txtRate.Value = ""

Else
    x = Application.VLookup(Val(Me.cmbItemID.Value), sh.Range("B:F"), 5, False)
    If Not IsError(x) Then Me.txtRate.Value = x
End If

End Sub

 

[gleamng]

How about

Private Sub cmbItemID_Change()
Dim sh As Worksheet
Dim x As Variant
Set sh = ThisWorkbook.Sheets("ProductMaster")

If Me.cmbItemID.Value = "" Then Me.txtRate.Value = ""

Else
    x = Application.VLookup(Val(Me.cmbItemID.Value), sh.Range("B:F"), 5, False)
    If Not IsError(x) Then Me.txtRate.Value = x
End If

End Sub

Thanks, it returned compile error: Else without If

 

[Fluff]

Try

Private Sub cmbItemID_Change()
Dim sh As Worksheet
Dim x As Variant
Set sh = ThisWorkbook.Sheets("ProductMaster")

If Me.cmbItemID.Value = "" Then
   Me.txtRate.Value = ""
Else
    x = Application.VLookup(Val(Me.cmbItemID.Value), sh.Range("B:F"), 5, False)
    If Not IsError(x) Then Me.txtRate.Value = x
End If

End Sub

 

[gleamng]

Try

Private Sub cmbItemID_Change()
Dim sh As Worksheet
Dim x As Variant
Set sh = ThisWorkbook.Sheets("ProductMaster")

If Me.cmbItemID.Value = "" Then
   Me.txtRate.Value = ""
Else
    x = Application.VLookup(Val(Me.cmbItemID.Value), sh.Range("B:F"), 5, False)
    If Not IsError(x) Then Me.txtRate.Value = x
End If

End Sub

Thanks a million, it worked ???

 

[Fluff]

You're welcome & thanks for the feedback.