PDA

View Full Version : Solved: VBA VLookup Help (Excel '97)



phendrena
02-26-2009, 09:01 AM
Hi there,

I trying to setup a VLOOKUP in a Userform.


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)


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


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


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
02-26-2009, 09:07 AM
Where is the lookup code stored?

phendrena
02-26-2009, 09:15 AM
Hi xld,

Dim nlook As Variant
etc

Declared in the initalise routine.

Bob Phillips
02-26-2009, 09:32 AM
Well, surely, at that point, the texboxes are blank.

phendrena
02-26-2009, 09:42 AM
That is a very good point xld!

How about this :-


'---------------------------------------'
'-- 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


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

Bob Phillips
02-26-2009, 09:49 AM
Okqy; qt this point you hqvent defined zs:

phendrena
02-26-2009, 09:59 AM
Okqy; qt this point you hqvent defined zs:Having a bad keyboard moment xld? :wot

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

Bob Phillips
02-26-2009, 10:03 AM
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
02-26-2009, 10:24 AM
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
03-10-2009, 06:58 AM
Hi,

Anyone able to help me with this one please?

Thanks,

phendrena
03-10-2009, 07:31 AM
Ok, instead of the above i've tried the following code :
Private Sub txtDealerNo_Change()
Set ws = Worksheets("Lookups")
txtDealerName.Value = WorksheetFunction.VLookup(txtDealerNo.Value, ws.Range("W14:Y639"), 2, 0)
End Sub
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
03-10-2009, 08:06 AM
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
03-10-2009, 09:27 AM
How does that help, it just skips the error, so it just avoids the problem?

phendrena
03-11-2009, 01:08 AM
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
03-11-2009, 01:27 AM
Try using an AfterUpdate event instead of a Change event

Bob Phillips
03-11-2009, 01:33 AM
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
03-11-2009, 06:06 AM
Hi,

I've changed it to an AfterUpdate event :
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 All working nicely.
Thank you for your suggestions and assistance.:thumb