Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Let's say that I have the following trivial task:

  • Write the first date of the months from January 2016 to June 2018 on a row
  • Find 01-January-2016 and color it in red
  • Use Range.Find()

Thus, I create a code, looping from 1 to 30 and writing the first date of each month. Then I use Rows(1).Find(CDate("01.01.2016")) or Rows(1).Find(DateSerial(2016,1,1)) and I consider my task almost ready.

I run the code and I see this in both Excel 2010 and Excel 2016:

enter image description here

Question: Is there any reason behind it? Or is the Range.Find() function documented to act like this?

Public Sub TestMe()

    Cells.Clear

    Dim cnt             As Long
    For cnt = 1 To 30
        Cells(1, cnt) = DateAdd("M", cnt - 1, DateSerial(2016, 1, 1))
        Cells(1, cnt).NumberFormat = "MMM-YY"
    Next cnt

    Dim foundRange      As Range
    Set foundRange = Rows(1).Find(CDate("01.01.2016"))
    'Set foundRange = Rows(1).Find(DateSerial(2016, 1, 1))  'the same false result
    'Set foundRange = Rows(1).Find("01.01.2016")             'does not find anything
    If Not foundRange Is Nothing Then
        foundRange.Interior.Color = vbRed
    End If

End Sub

In general, the Range.Find() has an optional After parameter, which is the first cell of the Range. In our case, the After parameter is omitted, thus it is considered to be A1 and it is checked last. If you stop the code after the first loop and you manually delete Nov 16 from Excel, then you continue the code, it will return the cell Jan 16 in red.

As far as November is considered found, it gives it back and it does not go further. The question is more like - in what logic is 1-November-2016 the same as 1-January-2016, even partially?

like image 240
Vityata Avatar asked Feb 22 '18 16:02

Vityata


People also ask

How do you subtract two dates in VBA?

DATEDIFF Function in VBA. The DateDiff function in VBA calculates the difference between two dates in days, months, quarters, and years. In Excel, finding the difference between two dates has many ways. You do not need a special formula to calculate the difference between two dates.

How do I calculate days between dates in Excel VBA?

Use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.

How do I change the date format of a range in VBA?

Click on Insert tab > select Module. Step 2: Write the subprocedure for VBA Format Date or choose anything to define the module. Step 3: Choose the range cell first as A1. Step 4: Then use the Number Format function as shown below.


2 Answers

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

like image 149
10 revs, 3 users 49% Avatar answered Sep 28 '22 07:09

10 revs, 3 users 49%


Not sure why it is finding 1 November, but you can fix it by specifying a value for the optional LookAt argument of the Find() method:

Set foundRange = Rows(1).Find(CDate("01.01.2016"), Lookat:=xlWhole)
like image 42
Excel Developers Avatar answered Sep 28 '22 08:09

Excel Developers