'Why is VBA Find unable to find Date values within a range

I am using VBA Find function to search a variable of type Date within a range of Excel cells. Below is my code:

Public SprintDate As Date

In my Excel worksheet, I have cells I3 thru X3 that show dates like below. :

Additionally, the cells J3 thru X3 have a formula that adds 21 days to the value of I3 (see second picture below)

The cells J3 thru X3 are also of Date Format in Excel like below:

This is the code that I have to do the search:

                sSprintName1 = Trim(Split(JSONObj_Story("issues")(k)("fields")("customfield_10000")(JSONObj_Story("issues")(k)("fields")("customfield_10000").Count), "(+3 weeks)")(0))
                sSprintName2 = Split(sSprintName1, ",")(3)
                sSprintName3 = Split(Trim(sSprintName2), "=")(1)
                sSprintDay = Split(Trim(sSprintName3), "-")(2)
                
                sSprintName4 = Trim(Split(JSONObj_Story("issues")(k)("fields")("customfield_10000")(JSONObj_Story("issues")(k)("fields")("customfield_10000").Count), "endDate")(0))
                sSprintFullDate = Left(Split(sSprintName4, "startDate=")(1), 10) ' 2022-03-15
                sSprintMonth = Right(Left(sSprintFullDate, 7), 2)
                
                sSearchKey = sSprintMonth & "/" & sSprintDay & "/" & Year(Now())
                SprintDate = Format(sSearchKey, "m/d/yyyy")

Please ignore the variables (of type String) sSprintName1,sSprintName2, sSprintName3, sSprintName4. They are being used to extract Month and Date values from the JSON response.

Here is the Find function code that I have:

Set rng1 = Worksheets("Roadmap").Range("G3:X3").Find(SprintDate, LookIn:=xlValues)

I have searched on this site and found some similar questions but I am afraid they have not helped me much:

Resources from here:

Unable to find date using VBA .find

VBA, goto cell with a certain value (type: date)

excel vba

Solution 1:^[1]

Worksheets("Roadmap").Range("G3:X3").Find(DateSerial(Year(Date), CLng(sSprintMonth), CLng(sSprintDay)), , xlValues, xlWhole)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Jahanzaib Sehar

'Why is VBA Find unable to find Date values within a range

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]