VBA to find first occurrence of today's date and insert row above-CodePudding

I have the following code to insert a row above the first occurrence of the word Scheduled in column D and then find the first occurrence of the word Roadblocked and insert a row above that, but when I try to find the first occurrence of today's date in Column C and insert a row above it doesn't work. Any help would be greatly appreciated. I plan to continue the code with finding tomorrow's date and insert row above, etc

Dim cl As Range

Set cl = Range("D:D").Find("Scheduled")
If Not cl Is Nothing Then cl.Select
    ActiveCell.Offset(0).EntireRow.Insert
    ActiveCell.Offset(0, -3).FormulaR1C1 = "SCHEDULED"

Set cl = Range("D:D").Find("Roadblock")
If Not cl Is Nothing Then cl.Select
    ActiveCell.Offset(0).EntireRow.Insert
    ActiveCell.Offset(0, -3).FormulaR1C1 = "ROADBLOCKED"

Set cl = Range("C:C").Find([Today()])
If Not cl Is Nothing Then cl.Select
    ActiveCell.Offset(0).EntireRow.Insert
    ActiveCell.Offset(0, -2).FormulaR1C1 = "DUE TODAY"

CodePudding user response：

Replace the line

Set cl = Range("C:C").Find([Today()])

with

Set cl = Range("C:C").Find(Date)

Hopefully, that will be enough. If not, make sure that your date formats are matching. Try a Debug.Print Date() to see if VBA agrees with the date format in your cells.