VBA Lookup

[rrenis]

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

[VBA]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[/VBA]

Can anyone see what i'm doing wrong??

Cheers,
rrenis

[Bob Phillips]

Does this work

[vba]

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
[/vba]

[rrenis]

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

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

Cheers,
rrenis

[rrenis]

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

[VBA]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[/VBA]

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]

Can you post a sample workbook?

[rrenis]

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!



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

Cheers,
rrenis

[Bob Phillips]

The Lookup code works fine for me.