Adding 1 to a cell contents

[Anakha]

Hi,

I'm sure this is a very simple question with a very simple solution but I hardly ever use Excel and I just can't seem to track down any help or guidance on how to do this.

What I'd like to do is put a number in a cell and then, when pressing some defined keys, add 1 to that number and then display the new number. For example, if the contents of a cell were 5 I'd like to highlight the cell, press my keys and get Excel to add 1 to the 5 and display the new cell contents as 6 and so on and so on.

Is that doable in Excel?

Any help and advbice would be gratefully received.

Many thanks.

Robert Slater.
Frustrated Civil Servant.

 

[Mudface]

You could do this via a spin button as follows: -

Open up the control toolbox and select the spin button control.

'Draw' it onto the sheet.

Right-click on the spin button, select properties and in 'Linked cell' type in the cell you want to change (e.g. A1). change Small Change to the value you want (or leave it at 1 if you want to add 1 each time). Close the properties window and the control toolbox and away you go .

 

[Guest]

Hi Anakha

Well, there is a couple of ways you could try.
Type 1 in any cell, select that cell and go Edit,Copy. Then select the cell or cells you want to add the 1 to and go Edit,Paste Special and select ADD.

You could also make a macro that runs on the click of a button or by keystrokes to do this. You will need to use this code to add 1 to the value of the cell or cells you select:

For Each cell In Selection
cell.Value = cell.Value + 1
Next
End Sub

To record a macro:
go to Tools, Macro, Record New Macro.
A dialogue box will open where you can name your macro and designate key strokes to run it. From then on every action you take will be recorded in code. Just select cell A1 on your worksheet.
go to Tools, Macro, Stop Recording.
Now use Alt+F11 to go to the VB Editor where you will see your macro code ... should say Range("A1").Select. Replace this line of code with the 3 lines of code above.
Use Alt+F11 to return to the worksheet.

Select a cell or range of cells then use the key strokes you selected and it should work.

One caution with macros, the undo button on your toolbar won't reverse any macro action.

Hope this helps
Good luck
Derek

 

[Anakha]

Hi,

Great, that has worked perfectly.

Many thanks for the help offered.

Robert Slater.
Frustrated Civil Servant.