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.

 

[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 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..

 

[kevin9999]

I would probably take a different approach to this given the new requirements. There's probably a one-liner for this, however:

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

 

[Fuji]

Increment only numbers.

    With Sheets("sheet1").[a1:a500]
        .Value = .Parent.Evaluate(Replace("if(isnumber(#),#+1,if(#<>"""",#,""""))", "#", .Address))
    End With

 

[urimagic]

I would probably take a different approach to this given the new requirements. There's probably a one-liner for this, however:

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.

 

[urimagic]

Increment only numbers.

    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.

 

[kevin9999]

Strange, it worked for me on a test sheet - but @Fuji 's code is better.

 

[Fuji]

You are welcome and thank for the feedback.

 

[Joe4]

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.

 

[urimagic]

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.