Thanks for the correction. (I missed the .offset stuff.)

Norman Jones wrote:

Hi Roy,

Not IsEmpty(vResult) Then
ThisNum If = vResult.Offset(0,1)

Try instead:

If not vResult is Nothing Then
ThisNum = vResult.Offset(0,2).Value
End If

Incidentally, if the date cells in the named data range are formatted as
dates, you should replace:

Set vResult = .Find(What:=CLng(DateIn)
with
Set vResult = .Find(What:=(DateIn)

Incorporating these modifications, your code worked for me and reads
something like:

Sub myLookup()
Dim vResult As Range
Dim DateIn As Variant
Dim ThisNum As Double

DateIn = InputBox("Enter Target Date")

If IsDate(DateIn) Then
DateIn = DateValue(DateIn)
Else
Exit Sub
End If
MsgBox "Date = " & DateIn

With Selection ' Sheet1.Range("Data")
Set vResult = .Find(What:=DateIn, After:=.Cells(1, 1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
End With

If vResult Is Nothing Then
MsgBox "not Found"
Else
ThisNum = vResult.Offset(0, 2).Value
MsgBox ThisNum
End If
End Sub

---
Regards,
Norman

"Roy Kirkland" wrote in message
...
OK, but if I do this, how do I get the value from the 2nd cell to the
right of the date? I tried adding a statement:

Not IsEmpty(vResult) Then
ThisNum If = vResult.Offset(0,1)

But still end up with error 91 on the 'ThisNum' line., and the program
stops there.

Roy

"Norman Jones" wrote in message
...
Hi Roy,

Try changing:

SearchDirection:=xlNext).Offset(0, 2)

to

SearchDirection:=xlNext)

---
Regards,
Norman



"Roy Kirkland" wrote in message
...
I tried it Dave, but still got the same error. The line with the Set
statement gives the error 91 (variable not set) when the program flow
reaches it. I've tried several variations of this code and am at a dead
end. Any links to various lookup routines?

Thanks

Roy

"Dave Peterson" wrote in message
...
vResult is actually a range (the cell where the date is found).

You could drop the .countif() and just use .find.

Option Explicit
Sub myLookup()
Dim vResult As Range
Dim DateIn As Variant

DateIn = InputBox("Enter Target Date")
If IsDate(DateIn) Then
DateIn = DateValue(DateIn)
Else
Exit Sub
End If
MsgBox "Date = " & DateIn

With Sheet1.Range("Data")
Set vResult = .Find(What:=CLng(DateIn), After:=.Cells(1, 1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Offset(0, 2)
End With

If vResult Is Nothing Then
MsgBox "not Found"
Else
MsgBox vResult.Value
End If

End Sub

And sometimes dates are easier to find if you search for the numeric
representation (clng(datein)).

And I changed the name of your sub. =Lookup() is a worksheet function
and I
don't like to use words that excel likes to use.

Roy Kirkland wrote:

I'm trying to use the Find method to look up values in tables
according to
the date. The first column holds the date, and columns b - j hold the
numerical data. All columns and rows are in the named range "Data".
Here's
the code I've written -

Sub Lookup()
Dim vResult As Variant

datein = InputBox("Enter Target Date")
If datein 0 Then
datein = DateValue(datein)
End If
MsgBox "Date = " & datein

If WorksheetFunction.CountIf(Sheet1.Range("Data"), datein) 0 Then
With Sheet1.Range("Data")
vResult = .Find(What:=(datein), After:=.Cells(1, 1),
SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2)
End With
MsgBox vResult
End If
End Sub

The error tells is 91: Object or with variable not set - I have read
that
this may not have to do with the variables, but on mouseover, the
variable
vResult is empty. Any help would be appreciated.

--

Dave Peterson







--

Dave Peterson