How do I add “+1” to all cells in a user selected range?

Question

I need to have the user select a range of cells with their mouse and then run a macro to add +1 to that selected range. The range will often be different every time, so it cannot be defined.

Here's what I have so far, it just works on a single active cell, I cannot get it to work on a range selection, because I do not know what to define or function to utilize.

My code Follows:

Sub Add()
ActiveCell.Value = ActiveCell.Value + 1
End Sub

Answer 1

The below code uses the built-in Paste Special Add feature to add 1 to every cell in the selected range.

Sub AddOneToSelection()

    Dim rBlank As Range

    If TypeName(Selection) = "Range" Then

        'Put a '1' in an unused cell and copy it
        Set rBlank = ActiveSheet.Cells.SpecialCells(xlLastCell).Offset(1, 0)
        rBlank.Value = 1
        rBlank.Copy

        'Paste Special Add over the selection
        Selection.PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlAdd

        'Get rid of the copied cell
        rBlank.ClearContents

    End If

End Sub

The benefit of this over looping is that Paste Special Add treats formulas and values differently and you don't have to code that part yourself.

The downside is you increase your UsedRange by one row, if that matters to you.