Using Current Date Function THANKS!!!

[amwilber]

Hello all!
I am attempting to use some sort of code that will take in account the current date to tell whether a certain row within my excel spreadsheet it older than 10 days. I am thinking it can be done with a simple "if" statement, but I do not remember how to incorporate the "today" part of it.

As you can see in the example below, I have multiple tickets that I need to track. I want to have some sort of code that will take these different rows and analyze if the Recieved Date (column M) is older than 10 days. If it is older than 10 days I would like it to copy and paste the entire row associated with that ticket into "sheet 2".

As well, I would also like to possibly take a look at the "status" (column L). If the row shows that the status is "sndl2", i would like it to copy and paste that row into "sheet 3"

THANKS IN ADVANCE!


EDIT: Deleted HTML image - Please use mock data only...No real names or data. Also note that your baby blue text is really hard to read. Moderator
<script language="JavaScript" src="%3Ca%20href=" popup.js"="" colo="" puremis="" sun="">www.interq.or.jp http:></script><center><table" target="_blank"></table">
THANKS SOOOOOO MUCH!!!!
</center>

 

[Anthony47]

..as already pointed out by other Users.
I have revised the macro to be more robust.

Bye.

 

[amwilber]

From excel, press Alt-F11; this will open the vba editor; Menu /Insert /Module; then copy the following code and paste it into the right frame; Modify the "Destination=Sheets("Sheet2") to reflect your real destination sheet for the record to copy; return to excel and run the macro

Sub Macro1()
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Cells(Rows.count,1).End(xlUp).Offset(1, 0)
Next Cell
End Sub

Bye.

This one gave me an error saying "Next without For" hmmmmm

 

[Anthony47]

Did I made the macro "more robust"??

Sub Macro1()
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Cells(Rows.count,1).End(xlUp).Offset(1, 0)
END IF
Next Cell
End Sub

Note the END IF
Bye.

 

[amwilber]

Did I made the macro "more robust"??

Sub Macro1()
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Cells(Rows.count,1).End(xlUp).Offset(1, 0)
END IF
Next Cell
End Sub

Note the END IF
Bye.

Now all that i get is a "type mismatch", thank you sooo much for the help

 

[Anthony47]

Grrrrr....
Correct this also:

Cell.EntireRow.Copy Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

See you..

 

[amwilber]

Grrrrr....
Correct this also:

Cell.EntireRow.Copy Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

See you..

Still getting "type Mismatch" Thanks

 

[Anthony47]

In this case I guess that the informatuion in col. M is not a "date", maybe a string?
Is M1 used for the header? if Yes then change this
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))
to this:

For Each Cell In Range("M2", Cells(Rows.Count, 13).End(xlUp))

Bye.

 

[amwilber]

Does anyone else have any code for this? I keep getting errors with all of the code that Anthony gives me...Anthony-thanks for trying buddy, I really do appreciate every bit of help I get from you guys!

 

[Anthony47]

Let me try to help more...
Did you evaluate my guess that column M does not contain a date (in excel format) but a string?
If you format col M as "number", do the cells look numbers or something else? have a look to all the cells, from the first (either M1 or M2, depending on your code) to the last one used in that column.

Bye.

 

[Jonmo1]

In this case I guess that the informatuion in col. M is not a "date", maybe a string?

I think Anthony is on the right track here. That's what type mismatch means. It's likely that the dates in column M are NOT valid excel dates...Or at least some of them aren't...

To test if that's the problem, put this formula in your sheet somewhere
=ISNUMBER(M1) and fill down to the end of data.
Does that return TRUE for ALL dates in column M?


This may help account for headers or anything else that's not actually a date..

Sub Macro1()
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))
    If IsDate(Cell.Value) Then
        If (Date-Cell.value)>10 then
            Cell.EntireRow.Copy Destination:=Sheets("Sheets2").Cells(Rows.count,1).End(xlUp).Offset(1, 0)
        End If
    End If
Next Cell
End Sub