Is there a concise way to do Range("A1:A10").Value = Range("A1:A10").Value + 1 in vba

Question

I have a bit of problem working with range variable. for example, I can directly do assignment on a range of cells like this:

Range("B1:B10").Value = Range("A1:A10").Value

However, is there a concise way to do increment of range of values then assignment, following is what i mean but resulted in type-mismatch error

Range("A1:A10").Value = Range("A1:A10").Value + 1 

Edit: Ultimately what i want to implement is a scroll_button, when _UP is clicked, all the cells in the range increase value by 1, when _Down is clicked, all the cells in the range decrease value by 1. What's the most efficient way to do this?

Answer 1

This is what I'd do if you have a large chunk of data:

Option Explicit
Sub Increase_Decrease(UpOrDown As Long)

    Dim LastRow As Long, i As Long
    Dim arr As Variant

    With ThisWorkbook.Sheets("MySheet") 'Change MySheet for your sheet
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Range("A1", .Cells(LastRow, 1)).Value
        For i = 1 To UBound(arr)
            arr(i, 1) = arr(i, 1) + UpOrDown
        Next i
        .Range("A1", .Cells(LastRow, 1)).Value = arr
    End With

End Sub
Sub Increase()

    Increase_Decrease 1

End Sub
Sub Decrease()

    Increase_Decrease -1

End Sub

Then you link your scroll up button to the Increase procedure, and the scroll down button to the decrease button.

Answer 2

You can do this without VBA as well.

Demonstration setup will be

Insert form control from Developer >> Insert >> Form Controls >> Scroll Bar.

Parameters will be Minimum Value = 1 Maximum Value = 100 Incremental Change = 1 Page Change = 10 Cell Link = $B$1

And then insert following formula in cell A1: =ROW()-$B$1

Copy down.