Vlookup on a VB Form
 

I am trying to do a vlookup using the data from a form and a combobox but get
a 1004 error. Can someone explain why this generates an error on a form, but
works in the immediate window?

tbxTextBox.Value = Application.WorksheetFunction.VLookup(Int(Now() -
cbxComboBox.Value), Range("NamedDataRange"), 5, False)

Set to the onClick event of cbxComboBox, when someone picks a preset value
like 1 or 10, this triggers the vlookup to the named range and should return
the value to the text box tbxTextBox.

Vlookup on a VB Form
 

Here is code that does the same thing without using a worksheet function.

Set c = Range("NamedDataRange").Find( _
what:=Int(Now() - cbxComboBox.Value), _
LookIn:=xlValues)
If Not c Is Nothing Then
tbxTextBox.Value = c.Offset(0, 4)
End If

"headly" wrote:

I am trying to do a vlookup using the data from a form and a combobox but get
a 1004 error. Can someone explain why this generates an error on a form, but
works in the immediate window?

tbxTextBox.Value = Application.WorksheetFunction.VLookup(Int(Now() -
cbxComboBox.Value), Range("NamedDataRange"), 5, False)

Set to the onClick event of cbxComboBox, when someone picks a preset value
like 1 or 10, this triggers the vlookup to the named range and should return
the value to the text box tbxTextBox.