ExcelBanter - How to find date in a range?

Hi,

I've tried to use find method to look for a date in a range but it wa
not working. Here is my situation.

1. I've a date range in D3:D123
2. For row 4 and and below, I've StartDate in column B and EndDate i
column C
3. I like to compare in column B and column C with the date in th
range and give me the column number. For example if the date in colum
B match with the date in F3, then I will get a column no = 6 as
result of column F.
4. There was no error but I got nothing as a result.

Below is my not working code. I've tried two different method to fin
for date in B and C but nothing is working.

Any help are greatly appreciated. If there is other better method t
archieve this result, please give me advise.

Best regards,
hideki

Sub FindDateColumn()

Dim rngDate As Range
Dim rngCell As Range
Dim lngStartRow As Long
Dim lngLastRow As Long
Dim lngRow As Long
Dim datStart As Date
Dim datEnd As Date
Dim lngSDateCol As Long
Dim lngEDateCol As Long

Set rngDate = Range("D3:D123")

For lngRow = lngStartRow To lngLastRow
If Cells(lngRow, "A") < "" Then 'only when column A contain
value

'Get B and C date value
If IsDate(Cells(lngRow, "B")) Then datStart = Cells(lngRow
"B")
If IsDate(Cells(lngRow, "C")) Then datEnd = Cells(lngRow, "C")

'If date in column B not error get the column no.
If datStart < "0:00:00" Then
lngSDateCol = rngDate.Find(What:=datStart
LookIn:=xlValues).Column
End If

'If date in column C not error get the column no.
If datEnd < "0:00:00" Then
Set rngCell = rngDate.Find(What:=CDate(datEnd)
after:=Range("D3"), LookIn:=xlFormulas, searchorder:=xlByColumns)
If Not rngCell Is Nothing Then
lngEDateCol = rngCell.Column
End If
End If
End If
'for debug purpose
Debug.Print lngRow & " Start Date: " & lngSDateCol
Debug.Print lngRow & " End Date: " & lngEDateCol
Next
End Su

--
hidek
-----------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890
View this thread: http://www.excelforum.com/showthread.php?threadid=48831