For Each - Invalid Range

[mharper90]

I'm trying to use a For Each macro to erase dates in a range that are greater than Today. The range is C3 and D3 to Lr (I have Lr set earlier in the macro and it works other places for the same purpose). I keep getting an invalid range when I use:

Dim c As Range

For Each c In ws2.range("C3:D" & Lr)
        If c.value > Today Then
              c.value = ""
        Else
        End If
Next

But if I replace the range with "ws2.range("C:C")", it works for that range.

I'm probably making a very rookie mistake. Greatly appreciate any help. Also, I'm very open to alternative methods of accomplishing the same idea if someone has a better method.

 

[Michael M]

Can you supply ALL the code so we don't rewrite it differently to what you already have please??

 

[mharper90]

Unfortunately I don't have it with me. This file is stored on a high-security network at work with no access to the internet. I can only get to the internet from home. I can print out the code and type the whole thing in (I usually do for my more complex questions), but was hoping that someone could help with this snippet by itself. Sorry

 

[Michael M]

Try using Date rather than today....which isn't a valid VBA function
If it's a Date AND Time use Now() instead of date

Sub MM1()
Dim c As Range, lr As Long, ws2 As Worksheet
Set ws2 = Sheets("Sheet1")
lr = Cells(Rows.Count, "D").End(xlUp).Row
For Each c In ws2.Range("C3:D" & lr)
        If c.Value > Date Then
              c.Value = ""
        Else
        c.Value = "ok"
        End If
Next
End Sub

 

[mharper90]

That seems oddly similar to what I was using, but had trouble with. I will double check it and try anything different I see. Looks like it should work! Thanks!

 

[Michael M]

Don't forget to change

 c.Value = "ok"

to whatever is relevent to you