I am attempting to create a macro that will put the current days date in one cell and then via an input box promt the user to input a number of years in the future and input that date in the cell below the first. I have got as far as below but am having difficulty returning the value of the string for use in a formula in the secnd destination cell. The first date cell is F6, the second is F7, I am not sure I am going about this the reight way, but I have set a formula in cell F7 as follows
=F6+(P1*365.25)

I am curious as to how I get the value input from the inputbox into the cell P1, or else if there is an easier way to do this. I note the dateadd function does not allow the option of years addition, otherwise I would use that instead.

Sub dateissue()
Range("f6") = Format(Date, "dd-mm-yyyy")
Dim Message, Title, Default, CERTLIFE As String
Message = "Enter Certification Period" ' Set prompt.
Title = "Certlifespan" ' Set title.
Default = "10" ' Set default.
' Display message, title, and default value.
CERTLIFE = InputBox(Message, Title, Default)
Range("P1") = String(CERTLIFE, 0)
End Sub
--
Roger W

How about this?

Days = Day(Range("F6"))
Months = Month(Range("F6"))
Years = Year(Range("F6")) + CertLife

Range("F7") = DateValue(Months & "/" & Days & "/" & Years

--
Message posted from http://www.ExcelForum.com

Not sure how to tie it in, when I attach the code to the end of my macro it complains that 'days' is not a defined variable, I suspect it will do the same with months and years also.
--
Roger W


"RogerNZ" wrote:

I am attempting to create a macro that will put the current days date in one cell and then via an input box promt the user to input a number of years in the future and input that date in the cell below the first. I have got as far as below but am having difficulty returning the value of the string for use in a formula in the secnd destination cell. The first date cell is F6, the second is F7, I am not sure I am going about this the reight way, but I have set a formula in cell F7 as follows
=F6+(P1*365.25)

I am curious as to how I get the value input from the inputbox into the cell P1, or else if there is an easier way to do this. I note the dateadd function does not allow the option of years addition, otherwise I would use that instead.

Sub dateissue()
Range("f6") = Format(Date, "dd-mm-yyyy")
Dim Message, Title, Default, CERTLIFE As String
Message = "Enter Certification Period" ' Set prompt.
Title = "Certlifespan" ' Set title.
Default = "10" ' Set default.
' Display message, title, and default value.
CERTLIFE = InputBox(Message, Title, Default)
Range("P1") = String(CERTLIFE, 0)
End Sub
--
Roger W

So you have got the number of years in P1.
Try in f7 '=Date(year(f6)+p1,month(f6),day(f6))

Regards

Chris
"RogerNZ" wrote in message
...
I am attempting to create a macro that will put the current days date in
one cell and then via an input box promt the user to input a number of years
in the future and input that date in the cell below the first. I have got as
far as below but am having difficulty returning the value of the string for
use in a formula in the secnd destination cell. The first date cell is F6,
the second is F7, I am not sure I am going about this the reight way, but I
have set a formula in cell F7 as follows
=F6+(P1*365.25)

I am curious as to how I get the value input from the inputbox into the
cell P1, or else if there is an easier way to do this. I note the dateadd
function does not allow the option of years addition, otherwise I would use
that instead.

Sub dateissue()
Range("f6") = Format(Date, "dd-mm-yyyy")
Dim Message, Title, Default, CERTLIFE As String
Message = "Enter Certification Period" ' Set prompt.
Title = "Certlifespan" ' Set title.
Default = "10" ' Set default.
' Display message, title, and default value.
CERTLIFE = InputBox(Message, Title, Default)
Range("P1") = String(CERTLIFE, 0)
End Sub
--
Roger W