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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
One way. Try changing:
If UCase(c.Value) = "11/29/2005" Then
to
If UCase(c.Value) = DateSerial(2005,11,29) Then
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,792
Messages
6,104,000
Members
447,889
Latest member
birdman15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top