Problem with lookups via VBA

[Roy Kirkland]

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.

 

[DMoney]

try this -- i believe the problem was putting the variable called Data in ""
If WorksheetFunction.CountIf(Sheets("Sheet1").Range(Data), datein) > 0 Then

 

[Roy]

Thanks, but I pasted in the code and got a new error - RunTime 1004 -
Application-defined or Object-defined error - I'm still trying
variations on it though.

Roy




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Dave Peterson]

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]

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

 

[Norman Jones]

Hi Roy,

Try changing:

to

---
Regards,
Norman

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

 

[Roy Kirkland]

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:

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

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

Roy

Hi Roy,

Try changing:

 

[Norman Jones]

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

 

[Roy Kirkland]

Hi Norm -

I recorded the find macro, and with a little tweaking, it is working
perfectly. The code you have is almost identical to what I've come up
with - the differences being only that I know what I'm going to do with the
results. Thanks for all your help.

Roy

 

[Dave Peterson]

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