Input Boxes & getting tied up in knots with strings!

R

RogerNZ

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
 
M

MSP77079

How about this?

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

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

RogerNZ

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.
 
C

Chris.F

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

Regards

Chris
RogerNZ said:
I am attempting to create a macro that will put the current days date in
Click to expand...
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
Click to expand...
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.
 
Top