CODEWITHSUNDEEP
excelvba
Jrules80
Jrules80

Reputation: 178

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. : Picture 1

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

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

Picture 3

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)

Upvotes: 1

Views: 294

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

Application.Match vs Find With Dates

sSearchKey = sSprintMonth & "/" & sSprintDay & "/" & Year(Date)
Dim SprintDate As Date
SprintDate = CDate(sSearchKey)

Dim rg As Range: Set rg = Worksheets("Roadmap").Range("G3:X3")

' The Find Method

' The Find method will fail:
' when the worksheet is filtered,
' when there are hidden rows or columns (when 'xlValues'),
' and when there are merged cells (possibly your case).
Set rng1 = rg.Find(SprintDate, , LookIn:=xlValues, LookAt:=xlWhole)
If Not rng1 Is Nothing Then
    Debug.Print rng1.Address(0, 0), rng1.Value
Else
    Debug.Print "Nope."
End If

' The Application.Match Function

Dim dIndex As Variant
dIndex = Application.Match(CLng(SprintDate), rg, 0)
If IsNumeric(dIndex) Then
    Set rng1 = rg.Cells(dIndex)
    Debug.Print dIndex, rng1.Address(0, 0), rng1.Value
Else
    Debug.Print "Nope."
End If

Upvotes: 1

Jahanzaib Sehar
Jahanzaib Sehar

Reputation: 59

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

Upvotes: 0

Related Questions