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.
 
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 Kevin, I found the spot...lol...please may I ask, I did not realize the code was going to increment blank cells as well...I was trying if statements but i'm failing. Can you just give me the line to insert maybe so the code will only increase numbers which are 0 or more. Not the blank cells as well....Thank you for assisting..
 
Upvote 0
I would probably take a different approach to this given the new requirements. There's probably a one-liner for this, however:

VBA Code:
Sub Increase_By_1()
    Dim a, b, i As Long
    a = Range("A1:A500")                    '<~~ Adjust range to suit
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = LBound(a, 1) To UBound(a, 1)
        If a(i, 1) <> "" And a(i, 1) <> 0 Then b(i, 1) = a(i, 1) + 1 Else b(i, 1) = a(i, 1)
    Next i
    Range("A1").Resize(UBound(b, 1)).Value = b
End Sub
 
Upvote 0
Increment only numbers.
Code:
    With Sheets("sheet1").[a1:a500]
        .Value = .Parent.Evaluate(Replace("if(isnumber(#),#+1,if(#<>"""",#,""""))", "#", .Address))
    End With
 
Upvote 0
Solution
I would probably take a different approach to this given the new requirements. There's probably a one-liner for this, however:

VBA Code:
Sub Increase_By_1()
    Dim a, b, i As Long
    a = Range("A1:A500")                    '<~~ Adjust range to suit
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = LBound(a, 1) To UBound(a, 1)
        If a(i, 1) <> "" And a(i, 1) <> 0 Then b(i, 1) = a(i, 1) + 1 Else b(i, 1) = a(i, 1)
    Next i
    Range("A1").Resize(UBound(b, 1)).Value = b
End Sub
Good day kevin9999,

Oops, this time your code did nothing. Clicking the button made no changes to the range. I tried Fuji's code, that worked perfectly. Thank you again for assisting. Stay blessed.
 
Upvote 0
Increment only numbers.
Code:
    With Sheets("sheet1").[a1:a500]
        .Value = .Parent.Evaluate(Replace("if(isnumber(#),#+1,if(#<>"""",#,""""))", "#", .Address))
    End With
Good day Fuji,

your code worked perfectly. I really appreciate the assistance and time spent. Stay blessed.
 
Upvote 0
urimagic,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.
 
Upvote 0
urimagic,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.
Hi Joe4,

I thought I did it right....i'm sorry, will be more attentive next time....thank you.
 
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