How to replace various numbers in a Microsoft Excel column with a value equal to +1?

Question

I have a column (Column A) with one number per row, about 200 rows down (Rows A1 to A200). Each day I need to advance every number in the column by one, effectively replacing the original number with a new one equal to +1. So one week from now, all 200 rows of numbers will be a value of 7 more than they are today.

Is there any way to do this other than manually advancing the numbers? I know I could use an =A1+1 type of formula, but wouldn't that require me to retain the original value of A1 indefinitely? Remember I need to "replace" A1 all the way down to A200 with a value of +1 and need to do this every day.

Any ideas or am I stuck manually replacing numbers?

Answer 1

theres a few ways to do this

1. the easiest way to do this:

- type the number 1 into an empty cell like B5 (or whatever)

- copy cell B5

- highlight A1 to A200

- right click on your highlighted column

- click paste special...

- click Add

- click ok

2 the fastest way

- record a macro

- just start the recording and follow the steps in 1

- then its just a button click

3 the 3rd easiest way (this would take a little work)

- do the math

- use the formula in A1

=today()

- format the cell as a number

- and you will get 39683

- then you subtract to get to the number you want (lets say you want the number 10) then you would use the formula

=TODAY()-39674+1

- then every day you open the sheet it will add 1

- the obvious problem with this is...it changes every day (whether you open it or not)

so i think #2 is the best way to go

your macro would look something like this

Sub add1()

'

' add1 Macro

'

'

    Range("B5").Select

    ActiveCell.FormulaR1C1 = "1"

    Range("B5").Select

    Selection.Copy

    Range("A1:A200").Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _

        False, Transpose:=False

End Sub

attach it to a button you can click when you want.

good luck

Answer 2

As usual, Expletiv has some excellent suggestions. I would agree with the option of using a macro, but I would not attach it to a button.  Buttons can get 'accidentally' clicked.  I would create a keyboard shortcut for the macro that would require definite intent to run it.

This macro will increment Column A by 1.

Sub Increment_ColA()

Dim rng As Range

Set rng = Range("A1:" & Range("A65536"). _

End(xlUp).Address(0, 0))

For Each cell In rng

cell.Select

cell.Value = cell.Value + 1

Next

[A1].Select

End Sub

Copy the macro to the clipboard.

Press, ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right.

Close back to Excel.

Go to Tools  >  Macro  >  Macros

Highlight the Increment_ColA macro, if it is not already highlighted.

Click:  Options

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

When you press CTRL +  your keyboard letter, the values in column A will increment by 1.