code to find dates

[David]

Greetings,
TIA for any help
I have a worksheet range: 01/01/1997, 01/02/1997, etc.
This is formatted as "mmm-yy" to give Jan-97, Feb-97, etc.

The following code from Reed Jacobson's 'Step by Step'
book fails to find the cell showing "May-97" as intended:

Dim myFind As Range
Set myFind = Rows(1).Find(What:="5/1/1997", _
After:=Range("A1"), _
LookIn:=xlFormulas _
, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Range("C1", myFind.Offset(, -1)).EntireColumn.Hidden = True

Changing the "5/1/1997" to "01/05/1997" etc. has no effect
(myFind =Nothing, still) Changing to "May-97" and changing
argument xlformulas to xlvalues works OK but is inflexible
and puts me at the mercy of the cell formatting. What is
the secret with dates?
David

 

[Dianne]

Instead of looking for a string, look for a variant:

Sub FindIt()

Dim rngFind As Range
Dim varFind As Variant

varFind = DateSerial(1997, 5, 1)

Set rngFind = Rows(1).Find(What:=varFind, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Range("C1", rngFind.Offset(, -1)).EntireColumn.Hidden = True

End Sub

 

[Steve Hieb]

I put random dates in several columns in the first row only, including
one w/ 5/1/1997 (date not string). For me, myFind found and returned
5/1/1997 like it should. Not sure what I'm missing, but the last line
of code seems odd. It appears to hide column "C" and any column to
the right of column "C" but to the left of wherever it finds 5/1/97.
This seems to be working as designed, although I'm questioning if this
was your intent. If this is really what you're doing and its still
not working, you might need to make sure the value is really a date
and/or convert it to one if its a string (see CDate in help).

Also, you might also try something like:

Dim myFind As Range, Target As Date
Target = #5/1/97# 'Notice its looking for a date not a string
"5/1/1997"
Set myFind = Rows(1).Find(What:=Target, _
....

Good luck,
Steve Hieb

 

[Guest]

Thanks Steve, that was a really helpful response.

 

[David]

Steve,
Target = #5/1/97# works fine, however, the dates being
searched appear on the sheet as May-97 and in the formula
bar as 01/05/97 for example, ie: this is a UK setup. The
problem is, Target = #1/5/97# not work. Any idea why?

 

[Steve Hieb]

Obviously, I'm guessing #1/5/97# is interpretted as January 5, 1997.
I'd try Dianne's suggestion instead. Haven't tested any of these, but
for May 1, 1997 try:

Target=DateSerial(1997, 5, 1) 'Year, Month , Day

or maybe

Target= CDate(5 &"/"& 1 &"/"& 1997)

"The day is before the month" .... next thing you know the UK will be
claiming a "metric system" is the way to go ... when will the madness
end??