Comparing a date in a macro to a cell

Mel_2

New Member
Joined
Nov 30, 2005
Messages
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,413
Office Version
  1. 365
Platform
  1. Windows
One way. Try changing:
If UCase(c.Value) = "11/29/2005" Then
to
If UCase(c.Value) = DateSerial(2005,11,29) Then
 

Mel_2

New Member
Joined
Nov 30, 2005
Messages
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,413
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,438
Messages
5,572,114
Members
412,441
Latest member
kelethymos
Top