MrExcel Publishing
Your One Stop for Excel Tips & Solutions

.Find method w/ Dates

Posted by yu-kuan on August 23, 2000 8:50 PM


I have a cell (say, A1) that the user enters a date into, like "2000/7/31". I would then like to use the .Find method to find this date in a range of cells, but for some reason the .Find method cannot seem to find it.

code as follows:

With Activesheet.Columns(1).Cells
Set cell = .Find(InputDate) 'InputDate is the cell w/ the date the user has entered.
if Not cell is Nothing then
row = cell.row
end if
End With

However, the .Find method is not working, as it is always returning pointing cell to Nothing, and I'm sure InputDate exists in the first column of the active sheet.

Any help will be greatly appreciated.


Posted by Thomas Venn on August 25, 0100 10:59 AM


In your "find" dialogue box, you have a choice of "formulas, values, or comments" in the "Look In" box. You need to choose values when you are trying to find a date.



Posted by Dave Rattigan on August 27, 0100 8:18 PM

use this logig with different variables:

thiscell = 0
thiscell = ActiveCell.Value
Set c = Nothing
Do Until IsEmpty(thiscell) = True
Set c = Nothing
ACVAL = ActiveCell.Value
With Sheets("Sheet2").Range("F:F")
Set c = .Find(ACVAL, LookIn:=xlValues)
If Not c Is Nothing Then
ActiveCell.Offset(1, 0).Select
c.Address <> firstAddres
GoTo readcell
End If
thiscell = ActiveCell.Value
End With