Increment Values in a range

urimagic

New Member
Joined
Jun 1, 2023
Messages
24
Office Version
  1. 2013
Platform
  1. Windows
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.
 
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.
VBA Code:
    [Z99] = 1
    [Z99].Copy
    
    Range("A1:A500").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
        
    Application.CutCopyMode = False
    
    [Z99].Clear
 
Upvote 0
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.

VBA Code:
Sub Increase_By_1()
    With Range("A1:A500")
        .Value = Evaluate(.Address & "+1")
    End With
End Sub
 
Upvote 0
Whenever opens.
To ThisWorkbook code module
Rich (BB code):
Private Sub Workbook_Open()
    ['sheet1'!a1:a500] = ['sheet1'!a1:a500+1]  '<--- change sheet1 to actual sheet name.
End Sub
 
Upvote 0
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.

VBA Code:
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.
 
Upvote 0
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.
VBA Code:
    [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.
 
Upvote 0
Whenever opens.
To ThisWorkbook code module
Rich (BB code):
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.
 
Upvote 0
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

VBA Code:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top