#1
|
|||
|
|||
Range method Find can't find dates
I have been using range.find successfuly until I tried to find a date. I have a simple workbook being an extract from the main project. Unfortunately I can't get any response from the attachment icon so I have listed the macro code under. The workbook range TestDate has dates in "A1:E1" the cells are in date format. The code using the Range.Find method fails to find the date. The more traditional For_Next loop does find it. I expect the solution will be simple but I have tried everything I can think of. The LookIn parameter made no difference.
Sub MyMacro() Dim ldDate As Date ldDate = Range("B1").Value Range("TestDate").Select Set c = Selection.Find(ldDate) ', LookIn:=xlValues) If Not c Is Nothing Then MsgBox "found " & ldDate Else MsgBox "Not found " & ldDate End If For i = 1 To 5 With Range("TestDate") If .Cells(1, i) = ldDate Then MsgBox "found " & ldDate Else MsgBox "not found " & ldDate End If End With Next 'i End Sub 'MyMacro |
#2
|
|||
|
|||
To answer my own question it appears that the VBA method "find" does not work with dates. The reason is that Excel dates are represented as numbers (seconds since whenever) but not so in VBA. There are workarounds which I was given by the MS Community to be found at https://stackoverflow.com/questions/45639660/ex...
but they are messy and in my case a for_next loop is an adequate solution. Last edited by jmcsa3; 05-02-2020 at 07:01 AM. Reason: typos |
Tags |
dates excel fields, find, range |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do you use the find and replace tool to find dates and times in Excel 2013? | Jules90 | Excel | 3 | 04-14-2020 07:40 PM |
what method to find all cells paste linked to a certain cell ? | DBenz | Excel | 1 | 06-28-2018 12:16 PM |
Find if Date range falls within another range | Triadragon | Excel | 3 | 05-02-2016 11:48 AM |
find IP in range / find number between numbers | gn28 | Excel | 4 | 06-14-2015 03:46 PM |
Find and Replace: Dates | Attirb | Word | 2 | 04-13-2011 09:56 AM |