Problems with Find in VBA/Excel

[Roy Kirkland]

I posted this to another excel group, but neither of the responses I had did
any good. I'm trying to get data from the cells to the right of the date
column in a 10 column table sorted by date. This is the code that I'm
trying to use:

Sub KSBLookup()
Dim vResult As Range
Dim datein As Date

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")
Set vResult = .Find(What:=(datein), After:=.Cells(1, 1),
SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(0, 2)
End With
MsgBox vResult.Value
End If

End Sub

As you may guess, I am getting error 91. Do I need to declare the range
name itself as a variable? The entiore set of values is the range. The
values in the columns other than the date column are integers. I have tried
several things - but get the same error most of the time,k and the error
isn't telling me anything. On mouseover, vResult appears empty. The
message boxes are in only to tell me if I am getting the correct result. I
have taken out the If statement (and its corresponding End If) but this does
not help either. Any help on this would be appreciated..

Roy

 

[Roy Kirkland]

I tied it and I got the same thing again. I thought it would work too. You
think I should create a new module and try it in there? I knowit doesn't
make much sense, but I've seen wierder -

Roy

 

[JulieD]

Hi Roy

I just checked out Dave Peterson's reply to you in .programming and it seems
like it should work, how did you go with what he suggested?

Cheers
JulieD

 

[JulieD]

Hi Roy

i had to play around with Dave's code to get it to work ... this now works
for me:
---
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 Sheets("Sheet1").Range("Data")
Set vResult = .Find(What:=Format(DateIn, "dd/mm/yy"), _
LookIn:=xlValues, _
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

 

[Roy Kirkland]

I am using Excel 2000 - do you think that may be why mine is not working? I
was reading the date before, but the variable vResult still reads as nothing
on mouseover. I get a negative number (-4163) on mouseover of xlValues -
maybe this means something to someone.

Roy

 

[JulieD]

Hi Roy

what is the cell references that "DATA" refers to?
is it on sheet1?
if you right mouse click on one of the dates in this range and choose format
/ cells - what formatting has the cell got?

have you tried recording a simple find macro using the macro recorder & edit
/ find .. this is how i eventually got the code to work

cheers
JulieD

 

[Roy Kirkland]

what is the cell references that "DATA" refers to?

is it on sheet1?

All values in sheet one are in the named range "Data"

if you right mouse click on one of the dates in this range and choose
format / cells - what formatting has the cell got?

Date format. mm/dd/yyyy. That portion of the sub (the msg box that returns
the input) works properly.

have you tried recording a simple find macro using the macro recorder &
edit / find .. this is how i eventually got the code to work

I'm working on that now - I'll let you know the results.

 

[Roy Kirkland]

Julie, Thanks. I did a Macro record and with a little tweaking, I got it to
do exactly what I wanted. I have it looping through all the cells with
values in order to send data to be processed, and it's working perfectly.

Roy

PS - I promise I won't cross-post again.

 

[JulieD]

Hi Roy

glad you solved it.

Cheers
JulieD

Julie, Thanks. I did a Macro record and with a little tweaking, I got it
to do exactly what I wanted. I have it looping through all the cells with
values in order to send data to be processed, and it's working perfectly.

Roy

PS - I promise I won't cross-post again.

 

[Dave Peterson]

The problem in my original suggestion was this:

Set vResult = .Find(What:=Format(DateIn, "dd/mm/yy"), _
LookIn:=xlValues, _
After:=.Cells(1, 1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Offset(0, 2)

If it wasn't found, then the .offset failed.

So it should have been:

Set vResult = .Find(What:=Format(DateIn, "dd/mm/yy"), _
LookIn:=xlValues, _
After:=.Cells(1, 1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

then later check to see if it was found:

If vResult Is Nothing Then
MsgBox "not Found"
Else
MsgBox vResult.offset(0,2).Value
End If

(Roy got help in the other thread, too.)