Date Macro

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Feb 2, 2005

• #2

Depends on how that value got into A1. Was it returned from a formula in A1? Is it manually entered? Part of a hyperlink? Imported from external data? Explain how it got there for a relevant suggestion.

 

Upvote 0

O

one_more_cave_dweller

Board Regular

Joined

Sep 27, 2004

Messages

118

• Feb 2, 2005

• #3

It is from a formula (Today()-1)

Thanks

 

Upvote 0

yee388

Well-known Member

Joined

Mar 7, 2004

Messages

1,374

• Feb 2, 2005

• #4

something along these lines?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

<SPAN style="color:#00007F">If</SPAN> Target.Address <> "$A$1" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">If</SPAN> Day(DateValue(Range("A1")) + 1) = 1 <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">'execute code</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

Upvote 0

PA HS Teacher

Well-known Member

Joined

Jul 17, 2004

Messages

2,838

• Feb 2, 2005

• #5

Yee, I'm still pretty new at this, but wouldn't the code only fire if A1 was selected at the time A1 changed? Or is that the point?

 

Upvote 0

O

one_more_cave_dweller

Board Regular

Joined

Sep 27, 2004

Messages

118

• Feb 2, 2005

• #6

Thanks for trying but that didn't do it. I need the macro to run if the date in cell a1 is tha last day of any month (Jan 31, Feb 28 etc.). Does anyone know if there is a code for last day of month?

Thanks

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Feb 2, 2005

• #7

Hi ... dweller:

Let us try ...

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [a1]) Is Nothing Then Exit Sub
    If Day([a1]) = Day(DateSerial(Year([a1]), Month([a1]) + 1, 0)) Then Macro
End Sub

The code will fire if the entry in cell A1 is changed so that it is the last day of the month. if it is to be used in a different way, modify it to suit accordingly.

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Feb 2, 2005

• #8

yee - -

Since this is a formula, a Change event won't do it; it'll require a Calc event.

omcd - -

Two questions:

(1)
Is this really for "any month" or only for the current month, or previous month?

(2)
How exactly is A1 formatted, what does the date look like when you see it?

 

Upvote 0

PA HS Teacher

Well-known Member

Joined

Jul 17, 2004

Messages

2,838

• Feb 2, 2005

• #9

Did the workbook change event in you previous post not meet your needs?
http://www.mrexcel.com/board2/viewtopic.php?t=128628

 

Upvote 0

O

one_more_cave_dweller

Board Regular

Joined

Sep 27, 2004

Messages

118

• Feb 2, 2005

• #10

THANKS YOGI!!! That did it

 

Upvote 0