Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to find dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to find dates
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 -- Dianne In , David typed: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to find dates
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to find dates
Thanks Steve, that was a really helpful response.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to find dates
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? -----Original Message----- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to find dates
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?? :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
Code to find code | Excel Discussion (Misc queries) | |||
what is the code for calender dates? | Excel Discussion (Misc queries) | |||
How do I find the earliest dates in a range of dates? | Excel Worksheet Functions | |||
to find number of days between 2 dates using vba code in excel | Excel Discussion (Misc queries) |