Vlookup for user form

[syps]

Hi Guys,

I have created a two Text box one for user to input values(INPUT1) and another to display(ANS) using Vlookup.

However, the Vlookup don't seem to work.


Private Sub INPUT1_Enter()
Dim X As Integer


If INPUT1.Value = X Then

ANS.Value = Application.VLookup(X, Range("A1:B6"), 2, False)
Else
MsgBox "Input value" & INPUT1.Value
ANS.Value = "Error"

End If
End Sub

Pls kindly advice
thanks in advance.

[Simon Lloyd]

Well you haven't said what ANS.Value is for nor have you declared ANS but as to your original question this line:
ANS.Value = Application.VLookup(X, Range("A1:B6"), 2, False)
should look like this:
ANS.Value = Application.WorksheetFunction.VLookup(X, Range("A1:B6"), 2, False)

[syps]

Hi Simon,

Thanks for the reply

ANS is a text box that display the vlookup result
INPUT1 a text box that allow user to key in the value.

I have change to what u have told me but that did not work.

It seem like the vlookup did not work

[chandansify]

Hi Simon,

Thanks for the reply

ANS is a text box that display the vlookup result
INPUT1 a text box that allow user to key in the value.

I have change to what u have told me but that did not work.

It seem like the vlookup did not work



Hi,

Please refer attached sample workbook and try this if it works for you.



Private Sub INPUT1_Change()
Dim X As Integer
On Error Resume Next

X = IIf(INPUT1.Value = "", 0, INPUT1.Value)

If X <> 0 Then

ANS.Value = Application.VLookup(X, Range("A1:B6"), 2, False)
Else
MsgBox "Input value" & INPUT1.Value
ANS.Value = "Error"

End If

End Sub

[syps]

Hi,

I have try and it seem like it is unable to do a Vlookup,
when i key a value in the INPUT1, the ANS.value show blank.

or is there otherway to work around?

Thanks

[tstav]

I have changed the event to INPUT1_AfterUpdate() and also the code is practically changed.

Private Sub INPUT1_AfterUpdate()
On Error Resume Next
ANS.Value = Worksheetfunction.VLookup(INPUT1.Value, Range("A1:B6"), 2, False)
If Err Then
MsgBox "Input value is " & INPUT1.Value
ANS.Value = "Error"
End If
End Sub

[syps]

Hi guys,

It seem like the vlookup did not work again..

My data is very simple from Range A1 to Range B6.
However it did not seem to work
Pls adv..

Thanks


Private Sub INPUT1_Enter()
On Error Resume Next
ANS.Value = Application.VLookup(INPUT1.Value, Range("A1:B6"), 2, False)
If Err Then
MsgBox "Input value is " & INPUT1.Value
End If
End Sub

[tstav]

syps, change the event to INPUT1_AfterUpdate() and try again please.
And it's Worksheetfunction.VLookup, not Application.VLookUp

[syps]

Hi Tatsv,

I have tried as well but it did not work,no error but did not display the vlookup value.


Private Sub INPUT1_Afterupdate()
On Error Resume Next
ANS.Value = WorksheetFunction.VLookup(INPUT1.Value, Range("A1:B6"), 2, False)
If Err Then
MsgBox "Input value is " & INPUT1.Value
End If
End Sub

[tstav]

Have you placed this code in the Userform's code module?

[Norie]

Try this.

ANS.Value = Application.WorksheetFunction.VLookup(Val(INPUT1.Value), Range("A1:B6"), 2, 0)

[syps]

Yes i right click the text box and select view code.

Is there any work aroynd solution?

[tstav]

Would you by any chance have a
Private Sub ANS_Change() event code or a
Private Sub ANS_Enter() event code
in the userform's code module, that change the value of the ANS textbox to ""?
This is getting pretty wild...

[syps]

Hi NOrie,

It work, btw why did i have to include VAL?

Thanks

[Norie]

Well a textbox contains text obviously and you are trying to look up a number, so you need to convert the text to a number.

Think about it like this - if you had numbers that were actually text on a worksheet and you tried to look them up against 'real' numbers then it wouldn't work.:)

[tstav]

syps, it's good it finally came to a happy end but how would you explain your post#9 answer:

I have tried as well but it did not work, no error but did not display the vlookup value

Since it was not finding the number you were inputting it surely must have always given you the messagebox of the IF ERR piece of code.

[Simon Lloyd]

Syps when entering code in your message please Highlight it and click the green VBA button at the top of your post window, it makes code easier to read and decipher!