Range.Find Date

[30percent]

Hi,

I have the following code that have been working for months. Suddenly today is 04/01/2019 the code stopped working. It couldn't find the cell that contain the date.

Set Rng = wb.Sheets("Sheet1").Range("A:A")

Set c = Rng.Find(Format(Date, "dddd, mmmm dd, yyyy"), LookIn:=xlValues)
        If Not c Is Nothing Then
            Set rng_Paste = wb.Sheets("Sheet1").Cells(c.Row, c.Column).Offset(0, 11)
            rng_Copy.Copy
            rng_Paste.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        End If

I tried the following code to see if I could work around it.

Set Rng = ThisWorkbook.Sheets("Sheet1").Range("A:A")

Application.FindFormat.NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"


Set c = Rng.Find(Date, LookIn:=xlValues, SearchFormat:=True)


If Not c Is Nothing Then
 Debug.Print c.Row
Else
 Debug.Print "Not Found"
End If

Output of the second group of code is "Not Found". Wonder if someone could help point out what's going on here. Why it is that my first group of code working all this time suddenly stopped working? and how I could rewrite it to work. I could confirm that the format of cell of the date that I'm trying to find is "[$-x-sysdate]dddd, mmmm dd, yyyy"

 

[Norie]

Does this work?

Set Rng = wb.Sheets("Sheet1").Range("A:A")

Res = Application.Match(Date, Rng, 0)

If Not IsError(Res) Then 
    Set rng_Paste = wb.Sheets("Sheet1").Cells(Res, c.Column).Offset(0, 11)
    rng_Copy.Copy
    rng_Paste.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End If

 

[30percent]

I tried both the following code:

[COLOR=#333333]Res = Application.Match(thisDate, Rng, 0)[/COLOR]

and

Res = Application.Match(Format(thisDate, "dddd, mmmm dd, yyyy"), Rng, 0)

Both got error 2042.

 

[Norie]

Try this.

Dim Res As Variant

    Set Rng = wb.Sheets("Sheet1").Range("A:A")

    Res = Application.Match(CLng(Date), Rng, 0)

    If Not IsError(Res) Then 
        Set rng_Paste = wb.Sheets("Sheet1").Cells(Res, c.Column).Offset(0, 11)
        rng_Copy.Copy
        rng_Paste.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If