Range.Find not making a difference between January and November (February and December) in VBA Excel

by

Whenever .Find(LookAt:=xlPart) is used upon a range of dates, it takes the dates not with their .Value2, but it silently converts them to String following the American date format – MM/DD/YY and looks into this string. The display format of the date in Excel is completely irrelevant, as long as the cell is formatted as a date.

Thus, every day of January can be found in November and every day of February can be found in December as a substring, making possible 58 (or 59 in a leap year) different mistakes within a calendar year:

List of dates for comparison

In order to avoid this mistake, the best solution is to look at xlWhole explicitly. If it is not referred, Range.Find() looks for partial string.

Another issue is where Range.Find starts. According to The Documentation It starts AFTER the supplied or default cell and only looks at the start cell after it cycles back.

The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

So, by not defining XlWhole and a start cell, the first cell that is searched is B1 not A1, and it finds the date partially before finding the correct date when it cycles around.

So setting the start cell at the end of the range will do it:

Set foundRange = Rows(1).Find(DateSerial(2016, 1, 1), [XFD1])

enter image description here

More Related Contents:

  1. VBA Convert date to week number
  2. Range.Find on a Date That is a Formula
  3. to make this code simpler
  4. VBA code takes long time to execute in excel
  5. Excel VBA deleting rows in a for loop misses rows
  6. VBA – destroy a modeless UserForm instance properly
  7. What is the lifetime of a global variable in excel vba?
  8. Download attachment from Outlook and Open in Excel
  9. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  10. Embed picture in outlook mail body excel vba
  11. How to check for empty array in vba macro [duplicate]
  12. Use autofilter on more than 2 criteria
  13. What is the function of FormulaR1C1?
  14. Compiler Error: User-defined types not defined
  15. Open a workbook using FileDialog and manipulate it in Excel VBA
  16. Vba Excel do vlookup from a closed file
  17. excel hyperlink to nothing
  18. How to add headers to a multicolumn listbox in an Excel userform using VBA
  19. How to send files via HTTP_POST with Excel using VBA?
  20. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  21. How to Add Date Picker To VBA UserForm
  22. Vba macro to copy row from table if value in table meets condition
  23. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  24. Set a cell value from a function
  25. How to break a long string into multiple lines
  26. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  27. simple vba code gives me run time error 91 object variable or with block not set
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment