Range.Find on a Date That is a Formula

by

Dates are tricky to find with the Range.Find method. One of the issues is that in VBA, dates are of the Date data type, but the worksheet does not have that data type. Rather the data type is a number that is formatted to look like a date.

One solution, if you can be certain of the format of the date on the worksheet, is to search for the string equivalent. Given your example, something like this will work:

Option Explicit
Sub GetDates()
    Const findDate As Date = #5/11/2017#
    Dim findStr As String

Dim R As Range, WS As Worksheet
Set WS = Worksheets("Sheet1")

findStr = Format(findDate, "dd-mmm")

With WS
    Set R = .Cells.Find(what:=findStr, LookIn:=xlValues, lookat:=xlWhole)
    If Not R Is Nothing Then MsgBox findDate & " found in " & R.Address
End With

End Sub

but it is not very robust since, in many cases, the user can change the format.

Another method that is more robust, would be to loop through the existing cells, looking for the numeric representation of the date (using the Value2 property):

Sub GetDates2()
    Const findDate As Date = #5/11/2017#
    Dim R As Range, C As Range, WS As Worksheet

Set WS = Worksheets("sheet1")
Set R = WS.UsedRange

For Each C In R
    If C.Value2 = CDbl(findDate) Then MsgBox findDate & " found in " & C.Address
Next C
End Sub

If you have a large range to search, this can be sped up by a factor of ten by reading the range into a VBA array and looping through the array.

More Related Contents:

  1. VBA Convert date to week number
  2. Range.Find not making a difference between January and November (February and December) in VBA Excel
  3. How do I get values from a hidden worksheet using VBA [closed]
  4. How to copy cells that contain certain text to another worksheet [closed]
  5. VBA Macro for copying and pasting columns on same excel sheet
  6. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  7. Levenshtein Distance in VBA [closed]
  8. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  9. How do I base64 encode a string efficiently using Excel VBA?
  10. Assign event handlers to controls on user form created dynamically in VBA
  11. Programmatic Access To Visual Basic Project Is Not Trusted
  12. Conditional Formatting using Excel VBA code
  13. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  14. VBA Integer Overflow at 70,000
  15. How to count up text of a different font colour in excel
  16. How to “flatten” or “collapse” a 2D Excel table into 1D?
  17. VBA Macro to compare all cells of two Excel files
  18. What does ReDim Preserve do?
  19. VBA – How the colon `:` works in VBA code with condition
  20. How to create a separate CSV file from VBA?
  21. VBA: How to change the value of another cell via a function?
  22. Running VBA from a HYPERLINK()
  23. lastrow and excel table.
  24. Open a workbook from VBA and disable Workbook_Open() code?
  25. Creating form programmatically in the module using vba
  26. How to filter listbox values based on a Textbox value
  27. Find and FindNext for Excel VBA
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment