Comparing a date in a macro to a cell

[Mel_2]

Hi:

I have a spreadsheet that I am trying to take some data from one workbook in the spreadsheet and move it to another workbook. I need to do this move based on a column called end date. If the end date is today -30 days then I want the row moved to the other sheet. The code I have works if I hardcode the date in but I can't seem to put a formula in and make it work. Could someone help? Thanks!! The code is below.

Sub Archive()
'Sheet module code, like: Sheet1
Dim myRng As Range, c As Range, myBott&

myBott = Range("A65536").End(xlUp).Row
Set myRng = Range(Cells(1, 16), Cells(myBott, 1))

For Each c In myRng

If UCase(c.Value) = "11/29/2005" Then _
c.EntireRow.Cut _
Destination:=Worksheets("Complete Events").Range("A65536").End(xlUp).Offset(1, 0)
Next c

Selection.AutoFilter Field:=1, Criteria1:="<>"


End Sub

 

[Joe4]

One way. Try changing:
If UCase(c.Value) = "11/29/2005" Then
to
If UCase(c.Value) = DateSerial(2005,11,29) Then

 

[Mel_2]

Hi, Thanks for the reply, I tried but it didn't work.

I don't know if I'm making any sense but I really just want to be able to compare the (c.value) to (TODAY()-30). I just don't know how to do it.

At one point I had an if statment in the spreadsheet that was doing the compare and putting the word Archive in a field and I was using Archive instead of the date to determine if the rows should be moved but that didn't seem as efficient as comparing the date in the vb code.

=IF(AND($Q58>0,$Q58<=(TODAY()-30)),"ARCHIVE"," ")

Any help is greatly appreciated.

 

[Joe4]

Sorry, I misunderstood your question. Is this what you are looking for?

If UCase(c.Value) = Date - 30 Then

Date will return the current day's date in VBA.