PDA

View Full Version : Lookup code in Userform errors out



Rocket2112
11-01-2012, 09:30 AM
Hello!

Lurker now user here. I am stuck. A first for me because you guys are always a huge help.

I am trying to create a user form in Excel 2003 which can either enter data or retrieve results based on a date. Entering the data is pretty straight forward but retrieving data has been the issue. I have been trying the Lookup argument and it doesn't work. I get this error "Unable to get the lookup property of the worksheetfunction class".

The following code is what I use:
---------
Private Sub Userform_Initialize()

Application.EnableEvents = False

lblDate.Caption = Date 'will display date based on Calendar Control selection

'retrieve the pH for selected date in West worksheet and display in Temp label
Sheets("West").Activate
lblTemp.Caption = Application.WorksheetFunction.Lookup(lblDate.Caption, _
Range("A2:A5000"), Range("c2:c5000"))

Application.EnableEvents = True

End Sub
----------

Here is the data in the worksheet

Date Temp pH Solids Nitrate
10/1/12 17 7.6 0.45 0.024
10/2/12 16 7.4 0.62 0.12
10/3/12 17 7.4 0.59 0.091
10/4/12 18 7.1 1.3 0.16


Thanks!

Teeroy
11-01-2012, 07:21 PM
Hi there,

I'd guess the error is because your Ranges aren't explicitly referenced to an object but since you want an exact match, how about using .Find then .Offset to return the value you are after? eg:

Set rng = ThisWorkbook.Sheets("West").Range("A2:A5000").Find(Me.lblDate.Caption)
Me.lblTemp.Caption = rng.Offset(0, 2).Value

Rocket2112
11-02-2012, 06:34 AM
Hi there,

I'd guess the error is because your Ranges aren't explicitly referenced to an object but since you want an exact match, how about using .Find then .Offset to return the value you are after? eg:

Set rng = ThisWorkbook.Sheets("West").Range("A2:A5000").Find(Me.lblDate.Caption)
Me.lblTemp.Caption = rng.Offset(0, 2).Value


I'll give this a shot. It looks like a good way of doing it. :thumb

I actually abbreviated the table. It goes out quite a few columns.