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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
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
63,302
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,004
Messages
5,834,833
Members
430,324
Latest member
bosphoruskid

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
Top