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