Finding Date Fails with .Find

[MWSC18]

Hello,

I am having a very strange issue where I select two dates (in the format mm/dd/yyyy hh:mm:ss) and a table is generated with data from the Start Date and End Date. There is a 'data storage' sheet, Sheet4, that has all potential dates to choose from in Row 4. There are 5 columns of data for each date. Data storage table example below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]10/25/2019 13:52:45[/TD]
[TD]10/25/2019 13:52:45[/TD]
[TD]10/25/2019 13:52:45[/TD]
[TD]10/25/2019 13:52:45[/TD]
[TD]10/25/2019 13:52:45[/TD]
[TD]10/28/2019 08:33:02[/TD]
[TD]10/28/2019 08:33:02[/TD]
[TD]10/28/2019 08:33:02[/TD]
[TD]10/28/2019 08:33:02[/TD]
[TD]10/28/2019 08:33:02[/TD]
[/TR]
[TR]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 3[/TD]
[TD]Category 4[/TD]
[TD]Category 5[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 3[/TD]
[TD]Category 4[/TD]
[TD]Category 5[/TD]
[/TR]
[TR]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[TD]Data 3[/TD]
[TD]Data 4[/TD]
[TD]Data 5[/TD]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[TD]Data 3[/TD]
[TD]Data 4[/TD]
[TD]Data 5[/TD]
[/TR]
</tbody>[/TABLE]


Below is part of the code for generating the consolidated table on Sheet5 (data comparison between the two dates):

Dim StartDate, EndDate As String
Dim recStartColumn, recEndColumn As Long


Sheet5.Range("B7:L58").ClearContents


StartDate = Sheet5.Range("D1").Text
EndDate = Sheet5.Range("D2").Text


MsgBox Sheet4.Rows(4).Find(StartDate, LookIn:=xlValues).Address 'Displays cell $CIR$4 correctly
MsgBox Sheet4.Rows(4).Find(EndDate, LookIn:=xlValues).Address 'Fails (Error 91). Should display $CIW$4.


recStartColumn = Sheet4.Rows(4).Find(StartDate, LookIn:=xlValues).Column 'Correctly finds Column 2280
recEndColumn = Sheet4.Rows(4).Find(EndDate, LookIn:=xlValues).Column 'Fails (Error 91). Should be Column 2285

It seems like if I select any date that has been generated today, the code fails on any step with the EndDate variable, even if the date and associated data is on Sheet4. For example, it will fail on the second MsgBox and, if the MsgBox's are commented out, it will fail when the recEndColumn line is executed. The failure in both cases is "Run-time error '91': Object variable or With block variable not set."

Anything regarding the StartDate variable is done successfully and the correct cell address for the first instance of the date is given for the MsgBox and the correct column number is generated as well.

This is code developed by someone else, and the file is very complicated, so in the short-term I just want a quick solution to this issue, but am planning on redesigning this in the future.

Thank you for any help!

 

[Yongle]

The code works for me without modifying anything but I ensured that the values in the 2 cells are indeed a match
- your problem is that they are not a match and that is what we need to understand and resolve

Amend these 2 lines in your code and test again

StartDate = Sheet5.Range("D1")
EndDate = Sheet5.Range("D2")

Let us know if how you get on

 

[MWSC18]

The code works for me without modifying anything but I ensured that the values in the 2 cells are indeed a match
- your problem is that they are not a match and that is what we need to understand and resolve

Amend these 2 lines in your code and test again

StartDate = Sheet5.Range("D1")
EndDate = Sheet5.Range("D2")

Let us know if how you get on

Thanks for the reply. I removed the .Text from those two lines, but now I get the same error with anything with 'StartDate' (the MsgBox and the recStartDate line).
One thing I noticed now is that the values in Row 4 look like they have two spaces between the date and time - in the code it appears as if there is only one space. I removed a space using backspace, but when clicking on the cell again, the second space comes back. I also tried to use the Custom Format on all of Row 4 to make it a single space mm/dd/yyy hh:mm:ss format, but it always goes back to two...

What is strange is that from what my coworker told me, she has used this file before without issue and then it just started having this code failure suddenly. I know you don't just get errors out of nowhere, and something had to have changed somewhere, but we don't know where. I've never personally used or developed this file before, so I have no idea what could have been done to it - just troubleshooting the issue...

 

[Yongle]

We need to understand all the values to explain the mismatch
- please put the formulas below in adjacent cells and let me know what each one returns

D1 and D2 values

A =ISNUMBER(D1)
B =T(D1)
C =ISNUMBER(D2)
D =T(D2)


Row 4 values

Does this formula return TRUE or FALSE (amend E4 to first cell in row 4 with date to be matched)
Copy the formula across all columns to see if all cells are consistent

E =ISNUMBER(E4)
F Do all cells return the same value ?

Does this formula return a blank string or the value of the cell
Copy the formula across all columns to see if all cells are consistent

G =T(E4)
H Do all cells return the same value ?

 

[MWSC18]

Sorry for the delayed reply; busy at work today and just got back to this file now...

D1 and D2 values

A =ISNUMBER(D1) = TRUE
B =T(D1) = Blank cell
C =ISNUMBER(D2) = TRUE
D =T(D2) = Blank cell


Row 4 values

E =ISNUMBER(E4) = TRUE
F Do all cells return the same value? = Yes

G =T(E4) = Blank cell
H Do all cells return the same value? = Yes

The fact that it cannot return the value for EndDate in the MsgBox is probably important to recognize... I also found out yesterday, that it works for certain dates but not for others. I haven't found a pattern though. There are currently 458 dates to choose from and they are all generated and stored the same way.

 

[Yongle]

Your reply suggests that D1,D2 and (relevant cells in) row 4 ALL contain numbers formatted as dates
- therefore "numbers" are not matching up

It may be a rounding issue - most times are "rounded"

Another possibility is that Excel is misinterpreting some date values
- Excel speaks "American" and "English" dates
- 12/10 can be interpreted by Excel as either 10th December or 12th October!
- is your PC set up with US dates as default in regional settings ?

- can you check the underlying number value of cell D2 and do the same for a cell in row 4 that should match but doesn't
- use formula =D2 somewhere and then format that value as a number to 0 places and do the same for a cell in row 4
- if the values differ then they are not the same date