Button to increase value of every cell by 1

February 21, 2011 at 10:31:09
Specs: Windows 7
Hi,
I'm doing a special project and what I need to do is create a button on my Excel spreadsheet that when clicked increases the value of 25 selected cells by one.
Lets say I have H3:H27 that I need to increase all by 1, how would I go about this. I took 3 different courses on Excel, but do not recall doing anything like this.

See More: Button to increase value of every cell by 1

Report •


#1
February 21, 2011 at 12:33:17
Assign this code to a button:

Sub AddOne()
 For Each sel In Selection
  Cells(sel.Row, sel.Column) = _
      Cells(sel.Row, sel.Column) + 1
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 21, 2011 at 13:28:17
Private Sub CommandButton1_Click()
Sub AddOne()
For Each sel In Selection
Cells(3, H) = _
Cells(27, H) + 1
Next
End Sub

that is my command atm, gives me an error saying:
Compile Error:
Expected End Sub


Report •

#3
February 21, 2011 at 14:33:22
I'm not sure why you changed just about everything I suggested.

You have 2 "start" lines:

Private Sub CommandButton1_Click()
Sub AddOne()

1 - You can only have 1. It can ether be a CommandButton sub or a "regular" sub but not both.

2 - You are telling the code to loop through the Selected range...

For Each sel In Selection

yet you are trying to specify (albeit incorrectly) a fixed range:

Cells(3, H) = Cells(27, H) + 1

As written, H is being used as a variable, which in your case would be equal to 0, so even if you didn't have the "End Sub" problem, the code would fail as soon as it tried to reference Column 0, which doesn't exist.

3 - Even if H was set to a valid value, e.g. 1 for Column A, the only thing your code would do is set e.g. A3 = A27+1 over and over again as it looped through the selected range.

Was there something about my code that you didn't like that made you make all of those incorrect changes?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 21, 2011 at 14:47:32
The only problem was that I wanted the specific cells from H3:H27 to be increased by 1, and not my selection. I wanted these fixed values to all increase by 1.

Report •

#5
February 22, 2011 at 09:37:39
Just select the cells H3:H27?

If anything, the original code allows flexibility should the fixed range H3:H27 ever need to be shifted for any reason. Just select the new range and click the button.


Report •

#6
February 22, 2011 at 09:55:32
Your OP said: "when clicked increases the value of 25 selected cells by one" so I offered code that works on a range of selected cells.

If you want to work on a specific range, then use this:

Sub AddOne()
 For Each sel In Range("H3:H27")
  sel.Value = sel.Value + 1
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question