Increment Values in a range

[urimagic]

a Very good day to you all,

Could someone please assist me with a code that will increment the current value in a range ("A:A500"). It can be with the aid of a button, it can be whenever the workbook is opened, it does not really matter the method as long as each value is incremented because of some kind of action. So this is for each cell in the range, whatever the value in that cell, it must increment by 1...Thank you kindly.

 

[6StringJazzer]

Use this code in the code for your button, Workbook_Open, or whatever.

Z99 represents the address of any unused cell. You can leave it, or use a different cell.
A:A500 is not a valid range. I assume you mean A1:A500.

    [Z99] = 1
    [Z99].Copy
    
    Range("A1:A500").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
        
    Application.CutCopyMode = False
    
    [Z99].Clear

 

[kevin9999]

You could attach the following code to a button on your sheet with the values. Like Jeff I'm unsure of the range you want so change the code to suit.

Sub Increase_By_1()
    With Range("A1:A500")
        .Value = Evaluate(.Address & "+1")
    End With
End Sub

 

[6StringJazzer]

You could attach the following code

Very elegant. I was hoping to find something this concise but

.Value = .Value + 1

raises an error.

 

[Fuji]

Whenever opens.
To ThisWorkbook code module

Private Sub Workbook_Open()
    ['sheet1'!a1:a500] = ['sheet1'!a1:a500+1]  '<--- change sheet1 to actual sheet name.
End Sub

 

[urimagic]

You could attach the following code to a button on your sheet with the values. Like Jeff I'm unsure of the range you want so change the code to suit.

Sub Increase_By_1()
    With Range("A1:A500")
        .Value = Evaluate(.Address & "+1")
    End With
End Sub

Hi Kevin9999,

Your code seemed more attractive and I decided to use it. Working great, thank you for your time, I do appreciate it.

 

[urimagic]

Use this code in the code for your button, Workbook_Open, or whatever.

Z99 represents the address of any unused cell. You can leave it, or use a different cell.
A:A500 is not a valid range. I assume you mean A1:A500.

    [Z99] = 1
    [Z99].Copy
   
    Range("A1:A500").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
       
    Application.CutCopyMode = False
   
    [Z99].Clear

Hi 6StringJazzer,

Thank you for your code and time spent, however I decided to go with another. I do appreciate.

 

[urimagic]

Whenever opens.
To ThisWorkbook code module

Private Sub Workbook_Open()
    ['sheet1'!a1:a500] = ['sheet1'!a1:a500+1]  '<--- change sheet1 to actual sheet name.
End Sub

Good day Fuji,
Thank you for your code and time spent, however I decided to go with another. I do appreciate.

 

[kevin9999]

Hi Kevin9999,

Your code seemed more attractive and I decided to use it. Working great, thank you for your time, I do appreciate it.

Happy to help & thanks for the feedback

 

[Scott Huish]

You may or may not need this but if you needed to use that in more than one place, you could write it as its own sub and pass the values to it.

For example. This would increment the values in A1:A500 BY 1 (the default value) and C3:C5 by 2

Public Sub Increment(r As Range, Optional n As Double = 1)
    r.Value = Evaluate(r.Address & "+" & n)
End Sub

Sub test()
Call Increment(Range("A1:A500"))
Call Increment(Range("C3:C5"), 2)
End Sub