The following is some code that I have written to help isolate some data
points within a set that corresond to a given date. Currently, the only way
I can get this to work is to enter these calculations into cells and name the
cells so that I can refer to the dates later in the code. Instead of
entering this data into z1:z3, I would like to just have the entered date and
calculated date stored via a public statement. But I am not successful.
How can I change the following to not rely on the cells in column Z?

Range("z1").Select
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the review period?")
ActiveCell.FormulaR1C1 = reviewbeg
Range("z2").Select

ActiveCell.FormulaR1C1 = _
"=RIGHT(YEAR(R[-1]C),2)&TEXT(R[-1]C-DATE(YEAR(R[-1]C),1,0),""000"")"
ActiveWorkbook.Names.Add Name:="begrevdate", RefersToR1C1:="='new'!R1C26"
ActiveWorkbook.Names.Add Name:="juliandate", RefersToR1C1:="='new'!R2C26"
Range("z3").Select
ActiveCell.FormulaR1C1 = _
"=date(YEAR(begrevdate)-1,month(begrevdate),day(begrevdate))"

TIA

You can assign the calculated dates and values directly to variables. VBA
uses the function "DateSerial" rather than "DATE" to manipulate dates and
that may be what messed you up.

Try the following code:


Sub tryit()

Dim reviewbeg As Date
Dim juliandate As String
Dim lastdate As Date
Dim begyear As Date

reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the review period?")
begyear = DateSerial(Year(reviewbeg), 1, 0)
juliandate = Right(reviewbeg, 2) & (reviewbeg - begyear)
lastdate = DateSerial(Year(reviewbeg) - 1, Month(reviewbeg),
Day(reviewbeg))

Range("Z1").Select
ActiveCell.Value = reviewbeg
ActiveCell.Offset(1, 0).Value = juliandate
ActiveCell.Offset(2, 0).Value = lastdate
End Sub

Stan Shoemaker
Palo Alto, CA


"Papa Jonah" wrote:

The following is some code that I have written to help isolate some data
points within a set that corresond to a given date. Currently, the only way
I can get this to work is to enter these calculations into cells and name the
cells so that I can refer to the dates later in the code. Instead of
entering this data into z1:z3, I would like to just have the entered date and
calculated date stored via a public statement. But I am not successful.
How can I change the following to not rely on the cells in column Z?

Range("z1").Select
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the review period?")
ActiveCell.FormulaR1C1 = reviewbeg
Range("z2").Select

ActiveCell.FormulaR1C1 = _
"=RIGHT(YEAR(R[-1]C),2)&TEXT(R[-1]C-DATE(YEAR(R[-1]C),1,0),""000"")"
ActiveWorkbook.Names.Add Name:="begrevdate", RefersToR1C1:="='new'!R1C26"
ActiveWorkbook.Names.Add Name:="juliandate", RefersToR1C1:="='new'!R2C26"
Range("z3").Select
ActiveCell.FormulaR1C1 = _
"=date(YEAR(begrevdate)-1,month(begrevdate),day(begrevdate))"

TIA

Stan,
Dateserial does not seem to be recognized. I am using Office XP. Do I need
some sort of add-in or something?

"stanshoe" wrote:

You can assign the calculated dates and values directly to variables. VBA
uses the function "DateSerial" rather than "DATE" to manipulate dates and
that may be what messed you up.

Try the following code:


Sub tryit()

Dim reviewbeg As Date
Dim juliandate As String
Dim lastdate As Date
Dim begyear As Date

reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the review period?")
begyear = DateSerial(Year(reviewbeg), 1, 0)
juliandate = Right(reviewbeg, 2) & (reviewbeg - begyear)
lastdate = DateSerial(Year(reviewbeg) - 1, Month(reviewbeg),
Day(reviewbeg))

Range("Z1").Select
ActiveCell.Value = reviewbeg
ActiveCell.Offset(1, 0).Value = juliandate
ActiveCell.Offset(2, 0).Value = lastdate
End Sub

Stan Shoemaker
Palo Alto, CA


"Papa Jonah" wrote:

The following is some code that I have written to help isolate some data
points within a set that corresond to a given date. Currently, the only way
I can get this to work is to enter these calculations into cells and name the
cells so that I can refer to the dates later in the code. Instead of
entering this data into z1:z3, I would like to just have the entered date and
calculated date stored via a public statement. But I am not successful.
How can I change the following to not rely on the cells in column Z?

Range("z1").Select
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning of
the review period?")
ActiveCell.FormulaR1C1 = reviewbeg
Range("z2").Select

ActiveCell.FormulaR1C1 = _
"=RIGHT(YEAR(R[-1]C),2)&TEXT(R[-1]C-DATE(YEAR(R[-1]C),1,0),""000"")"
ActiveWorkbook.Names.Add Name:="begrevdate", RefersToR1C1:="='new'!R1C26"
ActiveWorkbook.Names.Add Name:="juliandate", RefersToR1C1:="='new'!R2C26"
Range("z3").Select
ActiveCell.FormulaR1C1 = _
"=date(YEAR(begrevdate)-1,month(begrevdate),day(begrevdate))"

TIA

from the immediate window:

? dateserial(2004,11,30)
11/30/2004


It is part of VBA, so nothing additional required.

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
Stan,
Dateserial does not seem to be recognized. I am using Office XP. Do I
need
some sort of add-in or something?

"stanshoe" wrote:

You can assign the calculated dates and values directly to variables.
VBA
uses the function "DateSerial" rather than "DATE" to manipulate dates
and
that may be what messed you up.

Try the following code:


Sub tryit()

Dim reviewbeg As Date
Dim juliandate As String
Dim lastdate As Date
Dim begyear As Date

reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the beginning
of
the review period?")
begyear = DateSerial(Year(reviewbeg), 1, 0)
juliandate = Right(reviewbeg, 2) & (reviewbeg - begyear)
lastdate = DateSerial(Year(reviewbeg) - 1, Month(reviewbeg),
Day(reviewbeg))

Range("Z1").Select
ActiveCell.Value = reviewbeg
ActiveCell.Offset(1, 0).Value = juliandate
ActiveCell.Offset(2, 0).Value = lastdate
End Sub

Stan Shoemaker
Palo Alto, CA


"Papa Jonah" wrote:

The following is some code that I have written to help isolate some
data
points within a set that corresond to a given date. Currently, the
only way
I can get this to work is to enter these calculations into cells and
name the
cells so that I can refer to the dates later in the code. Instead of
entering this data into z1:z3, I would like to just have the entered
date and
calculated date stored via a public statement. But I am not
successful.
How can I change the following to not rely on the cells in column Z?

Range("z1").Select
reviewbeg = InputBox("What date (MM/DD/YYYY) will mark the
beginning of
the review period?")
ActiveCell.FormulaR1C1 = reviewbeg
Range("z2").Select

ActiveCell.FormulaR1C1 = _

"=RIGHT(YEAR(R[-1]C),2)&TEXT(R[-1]C-DATE(YEAR(R[-1]C),1,0),""000"")"
ActiveWorkbook.Names.Add Name:="begrevdate",
RefersToR1C1:="='new'!R1C26"
ActiveWorkbook.Names.Add Name:="juliandate",
RefersToR1C1:="='new'!R2C26"
Range("z3").Select
ActiveCell.FormulaR1C1 = _
"=date(YEAR(begrevdate)-1,month(begrevdate),day(begrevdate))"

TIA