PDA

View Full Version : VBA Lookup



rrenis
07-09-2010, 06:13 AM
Hi - I'm trying to populate a userform textbox with information from a worksheet, based on the input from a combobox in the userform. Unfortunately the VBA isn't working and I can't fathom why. Here's the code...

Private Sub ComboBox1_Change()
With Application
With .WorksheetFunction
TextBox2.Value = .Lookup(ComboBox1.Value, .Application.Worksheets("Summary Page").Range("C1:C100"), .Application.Worksheets("Summary Page").Range("E1:E100"))
End With
End With
End Sub

Can anyone see what i'm doing wrong?? : pray2:

Cheers,
rrenis

Bob Phillips
07-09-2010, 06:49 AM
Does this work



Private Sub ComboBox1_Change()
With Application
With .WorksheetFunction
TextBox2.Value = .Lookup(ComboBox1.Value, _
Worksheets("Summary Page").Range("C1:C100"), _
Worksheets("Summary Page").Range("E1:E100"))
End With
End With
End Sub

rrenis
07-09-2010, 11:50 AM
Hi xld - thanks for the reply! I've just tried it and it gives the following error...

"Unable to get the lookup property of the WorksheetFunction class"

Not sure why it's not working :think:

Lookup does not seem to appear in the VBA help which makes me think maybe I shouldn't be using lookup, or are there alternative ways of performing lookups in VBA? Any suggestions would be great as I seem to be going round in circles.. :doh:

Cheers,
rrenis

rrenis
07-09-2010, 12:21 PM
Hi - managed to come at it from a different angle. I googled using the find method to perform a lookup and came across some code posted by Tom Ogilvy which I've adapted as follows, just in case anyone else is looking to do something similar...

Dim ans As Integer, res As Variant
Dim rng As Range, rng1 As Range
ans = ComboBox1.Value

With Worksheets("Summary Page")
Set rng = .Range(.Cells(1, "C"), .Cells(1, "C").End(xlDown))
End With
res = Application.Match(ans, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res).Offset(0, 2)
TextBox2.Value = rng1.Value
Else
MsgBox "Not Found"
End If

This is working but not sure whether it's the best way to tackle this problem. If anyone's got a simpler way I'd be grateful if you could let me know as I'll be populating quite a few boses in the userform this way. Thanks.

Cheers,
rrenis

mdmackillop
07-09-2010, 12:22 PM
Can you post a sample workbook?

rrenis
07-09-2010, 01:08 PM
Hi mdmackillop - I've just finished adding all of the textboxes and it's running great so I'm happy to stick with the find method, was unsure at first how quickly it would populate.

Thanks for offering to have a look at this though!! And thanks to xld too!

:bow:

This has been nagging away at me after I left the office so I can now go and join the missus on the sofa!! Have a good weekend!!
:beerchug:
Cheers,
rrenis

Bob Phillips
07-09-2010, 01:38 PM
The Lookup code works fine for me.