Add Number to Range of Cells

Macro: Add Specific Number to Cells

This macro adds a specific number to all the selected cells. In this example, the Num variable is set to 7, to make it easy to add one week to cells that contain dates.

NOTE: Make a backup of your file, before using this macro.

Sub AddNumber()
Dim ws As Worksheet
Dim rngSel As Range
Dim rng As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lAreas As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Set rngSel = Selection

Num = 7

For Each rng In rngSel.Areas
  If rng.Count = 1 Then
     rng = rng + Num
  Else
      lRows = rng.Rows.Count
      lCols = rng.Columns.Count
      Arr = rng
      For i = 1 To lRows
         For j = 1 To lCols
            Arr(i, j) = Arr(i, j) + Num
         Next j
      Next i
      rng.Value = Arr
  End If
Next rng

End Sub

Add Number to Cells With VBA

This macro prompts you for a number, then adds that number to all the selected cells.

In the input box, the default number is set as 7, and you can overwrite that with any number (whole or decimal, positive or negative.).

NOTE: In the input box, the default number is set as 7, so it's easy to add a week to a group of dates. You can change the default, if you usually add a different number.

IMPORTANT: Make a backup of your file, before using this macro.

Sub AddNumberPrompt()
Dim ws As Worksheet
Dim rngSel As Range
Dim rng As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lAreas As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Dim strPrompt As String
Set rngSel = Selection
lAreas = rng.Areas.Count
strPrompt = "Enter number to add to selected cells"

On Error Resume Next
Num = InputBox(strPrompt, "Number to Add", 7)

If Num <> 0 Then
  For Each rng In rngSel.Areas
     If rng.Count = 1 Then
        rng = rng + Num
     Else
        lRows = rng.Rows.Count
        lCols = rng.Columns.Count
        Arr = rng
        For i = 1 To lRows
           For j = 1 To lCols
              Arr(i, j) = Arr(i, j) + Num
           Next j
        Next i
        rng.Value = Arr
     End If
  Next rng
End If

End Sub

Video: Add Number With Macro or Paste Special

To see the steps for using Paste Special, and the steps for modifying the Add Numbers macros, watch this video tutorial.

Download the Sample File

To download the sample file, click here: addnumbertocells.zip

The zipped file is in xlsm format, and contains macros. Enable macros, if you want to test the code in the sample file.

NOTE: Make a backup of your file, before using these macros.