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.