Increasing the date with a Macro

M

mattwilsonuk

In Cell *A1* i have a date. Cell B1 says "=A1"

I run a macro and added +7 to make cell B1 read "=A1+7".

When I run the macro again nothing happens.

I need to be able to run a simple macro and be able to increase th
date by 1 week each time the macro is run.

Can anyone help?

Thanks

Mat
 
T

Trevor Shuttleworth

Matt

correct me if I'm wrong but A1 hasn't changed. So if you keep setting B1 to
read "=A1+7" you're going to keep getting the same answer.

I think you need to change the increment: first time through, add 7; second
time through, add 14; and so on.

Regards

Trevor
 
C

Charles

Matt,

Are both A1 and B1 formated for Dates?
If so in cell B1= A1+7 it should increase 1 week. However if the dat
in A1 stays the same then no matter how many time you run your macr
the date will alway be the same.

HTH

Charle
 
C

cucchiaino

mattwilsonuk > said:
In Cell *A1* i have a date. Cell B1 says "=A1"

I run a macro and added +7 to make cell B1 read "=A1+7".

When I run the macro again nothing happens.

I need to be able to run a simple macro and be able to increase the
date by 1 week each time the macro is run.

Can anyone help?
Click to expand...


.....
x = 7
Range("B1").FormulaR1C1 = "=RC[-1]+" & x
......
 
M

mattwilsonuk

The date in A1 remains the same throughout.

I was hoping the Macro would create something as crude as

=a1+7+7+7+7+7+7+7+7+7+7+7" depending on the number of times I ran th
macro.

Is there another function I can use to acheive the desired result?

Thx for your input

Mat
 
M

mattwilsonuk

How can i make the macro run in increments?

Where does that formula x=7 range.... go?

Thx

Mat
 
C

cucchiaino

mattwilsonuk > said:
How can i make the macro run in increments?

Where does that formula x=7 range.... go?
Click to expand...

.....
x = Range("B1")
Range("B1").FormulaR1C1 = "=RC[-1]+" & 7
.....
 
M

mattwilsonuk

Isnt that VBA script because ive not really gone that far into excel an
cant say i know a great del about it - my macro is being created when
to to tools - macro - record new macro.

Mat
 
M

mattwilsonuk

I really appreciate you helping me out and ive been tinkering with th
formala you gave me - been moving it around.

I dont know what to do with it tho - I put it somewhere when i run th
macro?

Mat
 
B

Bob Phillips

Go into the VB IDE (Alt-F11)
Insert a new code module (Insert>Module)
Add this code

Sub Add7()
If Range("B1").Value = "" Then
Range("B1").VAlue = Range("A1").Value
Else
Range("B1").Value= Range("B1").Value + 7
End If
Range("B1").Numberformat = "dd mmm yyyy"
End Sub

Then every time you want to increase it, run the Sub acelled Add7.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top