how can I do this without actually putting into cells

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

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

 

[Tom Ogilvy]

from the immediate window:

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


It is part of VBA, so nothing additional required.

--
Regards,
Tom Ogilvy

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

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

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