Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2020, 06:42 AM
jmcsa3 jmcsa3 is offline Range method Find can't find dates Windows 10 Range method Find can't find dates Office 2019
Novice
Range method Find can't find dates
 
Join Date: Jan 2019
Location: UK
Posts: 14
jmcsa3 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-02-2020, 06:56 AM
jmcsa3 jmcsa3 is offline Range method Find can't find dates Windows 10 Range method Find can't find dates Office 2019
Novice
Range method Find can't find dates
 
Join Date: Jan 2019
Location: UK
Posts: 14
jmcsa3 is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
dates excel fields, find, range

Thread Tools
Display Modes


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
Range method Find can't find dates 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
Range method Find can't find dates Find and Replace: Dates Attirb Word 2 04-13-2011 09:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft