Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find with dates
I'm using the following, which is essentially a cut and paste for Excels VB
help, to search for a date held in a variable, CellDt. I create CellDt with DateValue, so I know it's valid. Plus the dates in column A are also valid (I've formatted the column to with Numbers | Date...etc., and I've set a variable to the contents of a few random cells and used TypeName to confirms it's a date. But C is always set to nothing; it never changes. If I change the range to the next column and search for a string, it does find it (C is set to the value found), so it seems it has something to do with the date(s) themselves...but I can't see what it might be. With Worksheets(1).Range("A1:A119") Set C = .Find(what:=CellDt, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do ' copy found item to variable, etc. ' . . . Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address < firstAddress Any thoughts? And thanks. Bert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Find with dates
Sub ZZZZ()
Dim celldt As Date celldt = DateValue("1/1/2000") With Worksheets(1).Range("A1:A119") Set c = .Find(what:=celldt, _ After:=Worksheets(1).Range("A119"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.ColorIndex = 6 Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With End Sub worked for me. -- Regards, Tom Ogilvy "Bert" wrote in message ... I'm using the following, which is essentially a cut and paste for Excels VB help, to search for a date held in a variable, CellDt. I create CellDt with DateValue, so I know it's valid. Plus the dates in column A are also valid (I've formatted the column to with Numbers | Date...etc., and I've set a variable to the contents of a few random cells and used TypeName to confirms it's a date. But C is always set to nothing; it never changes. If I change the range to the next column and search for a string, it does find it (C is set to the value found), so it seems it has something to do with the date(s) themselves...but I can't see what it might be. With Worksheets(1).Range("A1:A119") Set C = .Find(what:=CellDt, LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do ' copy found item to variable, etc. ' . . . Set C = .FindNext(C) Loop While Not C Is Nothing And C.Address < firstAddress Any thoughts? And thanks. Bert |
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 | |||
FIND and dates | Excel Worksheet Functions | |||
Find the three nearest dates | Excel Worksheet Functions | |||
Find future dates | Excel Discussion (Misc queries) | |||
How do I find the earliest dates in a range of dates? | Excel Worksheet Functions |