Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find date in a range?
I'm sorry, it was a stupid mistake. I've fixed it myself. It was the range that cause the result "nothing". The range should be "D3:DS3" not "D3:D123" as I wrote earlier. Please forgive me for the stupid posting. I don't know how to delete this post. cheers, hideki -- hideki ------------------------------------------------------------------------ hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903 View this thread: http://www.excelforum.com/showthread...hreadid=488312 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find product from date range | Excel Worksheet Functions | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
Find within Date Range | Excel Discussion (Misc queries) | |||
find sum if one col = ? within specific date range | Excel Discussion (Misc queries) | |||
Find date and copy range based on that date | Excel Programming |