VBA For Loop Not Matching Dates

[JessP]

In part of some code I'm working on, I'm trying to find the cell containing the first day of the previous month, then hiding everything from column U up to that column.

I've attempted a number of different methods, but nothing I've tried seems to work - it doesn't give an error, just skips over the for loop. Here's the relevant section of my most recent code - sorry, I didn't save all the attempts.

Dim cel as Range
Dim dtStart as Date
dtStart = DateSerial(Year(Date), Month(Date)-1,1

For Each cel In Range("U3:GA3")
    If cel.Value = dtStart Then
        cel.Offset(0, -1).Select
        Range(Selection, "U3").EntireColumn.Hidden = True
        Exit For
    End If
Next cel

In case this is affecting it, the dates in the file are formatted as =text(date(2017,3,1),"mmm"), but they should still be saved as a serial, right?

 

[Joe4]

Welcome to the Board!

In case this is affecting it, the dates in the file are formatted as =text(date(2017,3,1),"mmm"), but they should still be saved as a serial, right?

No, the TEXT function makes the value a string, not a date. If you want it to be a date, but just show the month abbreviation, use this:

=date(2017,3,1)

and then just apply the Custom Date format to the cell of "mmm".
Then it will be a date in the format that you want.

 

[JessP]

Thanks! Works perfectly - I probably should have realized it wasn't the code's fault by the time I'd written half a dozen that should have worked. I appreciate your help.

 

[Joe4]

You are welcome.
Glad I was able to help!