Solved: VBA VLookup Help (Excel '97)

[phendrena]

Hi there,

I trying to setup a VLOOKUP in a Userform.

[vba]
Dim nlook as Variant
Dim dLook As Variant
Dim sLook As Variant
Set ws = Worksheets("Lookups")
nLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 1, True)
dLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 2, True)
sLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 3, True)
[/vba]

Now, when the user enters the dealer number in a particular field it should then populate other fields :

[vba]
Private Sub txtDealerNoSendTo_Change()
Call sDName
End Sub

Private Sub txtDealerNoSendTo_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDName
End Sub

Private Sub sDName()
If Me.txtDealerNo.Value = nLook Then
Me.txtDealerName.Value = dLook

ElseIf Me.txtDealerNo.Value = "" Then
Me.txtDealerName.Value = ""

End If
End Sub
[/vba]

Where am I goinf wrong as when I enter a number into the field nothing happens. Would any be able to point me in the right direction.

Thanks,

[Bob Phillips]

Where is the lookup code stored?

[phendrena]

Hi xld,

Dim nlook As Variant
etc

Declared in the initalise routine.

[Bob Phillips]

Well, surely, at that point, the texboxes are blank.

[phendrena]

That is a very good point xld!

How about this :-

[VBA]
'---------------------------------------'
'-- Dealer No Field --'
'---------------------------------------'
Private Sub txtDealerNo_Change()
Call sDName
End Sub

Private Sub txtDealerNo_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDName
End Sub
Private Sub sDName()
Dim nLook As Variant
Dim dLook As Variant
Dim sLook As Variant
nLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 1, True)
dLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 2, True)
sLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 3, True)
If Me.txtDealerNo.Value = nLook Then
Me.txtDealerName.Value = dLook

ElseIf Me.txtDealerNo.Value = "" Then
Me.txtDealerName.Value = ""

End If
End Sub
[/VBA]

It now errors with 'Object Required' and highlights :
nLook = Application.VLookup(Me.txtDealerNo.Value, ws.Range("V14:X1033"), 1, True)

[Bob Phillips]

Okqy; qt this point you hqvenùt defined zs:

[phendrena]

Okqy; qt this point you hqvenùt defined zs:

Having a bad keyboard moment xld?

I didn't think i'd need to define 'ws' again as the userform_initalize is a public sub.

[Bob Phillips]

Wow, my keyboard mapping is well and truly screwed. Better re-boot.

Does your comment mean that you don't think ws is a problem? It won't be if ws is a module scope variable.

[phendrena]

Wow, my keyboard mapping is well and truly screwed. Better re-boot.

Does your comment mean that you don't think ws is a problem? It won't be if ws is a module scope variable.

Ok, the 'ws' is a problem, i have now added the following :

Set ws = Worksheets("Lookups")

Now, the code errors :- 'Could not set the property value. Type mismatch'
Highlights :- Me.txtDealerName.Value = dLook

The actual lookup list is a mixture of just numbers (12345), numbers and text (12345AA) and text and numbers (A1234).
I have tried to set this list to be formatted as text, numbers and general and still have the error.

Edit : VBA password = 0range, read-only= k1wifruit
(Spreadsheet is proteced but not with a password, look for the small up/down arrows to the far far right (Column S)

[phendrena]

Hi,

Anyone able to help me with this one please?

Thanks,

[phendrena]

Ok, instead of the above i've tried the following code :
[VBA]Private Sub txtDealerNo_Change()
Set ws = Worksheets("Lookups")
txtDealerName.Value = WorksheetFunction.VLookup(txtDealerNo.Value, ws.Range("W14:Y639"), 2, 0)
End Sub[/VBA]
I now get the error : Unable to get the Vlookup property of the WorksheetFunction class

Any suggestions on how I can get this to work?

Thanks,

[phendrena]

I'm such a pleb. I really am.

I have solved this problem by adding an error routine
On Error Resume Next

this has solved the problem and now works.

[Bob Phillips]

How does that help, it just skips the error, so it just avoids the problem?

[phendrena]

How does that help, it just skips the error, so it just avoids the problem?

I can see your logic.
The error occurs as soon as you start to type into the field so by adding the resume next it doesn't error and does the lookup correctly when the field is completed. I agree it's not a very good way to solve the problem but as a quick solution it'll do for now.

[mdmackillop]

Try using an AfterUpdate event instead of a Change event

[Bob Phillips]

Or the Exit event.

You should use the change event if you want to check after every character, otherwise validate using a button is how I do it, what we call cross-validation, validating across the form.

[phendrena]

Hi,

I've changed it to an AfterUpdate event :
[vba]Private Sub txtDealerNo_AfterUpdate()
Set ws = Worksheets("Lookups")
On Error GoTo ErrHandler
txtDealerName.Value = Application.WorksheetFunction.VLookup(txtDealerNo.Value, ws.Range("W14:Y1140"), 2, False)
cboScheme.Value = Application.WorksheetFunction.VLookup(txtDealerNo.Value, ws.Range("W14:Y1140"), 3, False)
Exit Sub
ErrHandler:
MsgBox "Dealer Not Found"
End Sub[/vba] All working nicely.
Thank you for your suggestions and assistance.