Add a specific amount to the numbers in a range of cells, either manually, or with a macro.
To increase the amounts in a range of cells -- adding the same amount to each cell -- you can use the Paste Special command.
For example, follow these steps to increase the amounts by 10, in cells A2:B6
To see the the steps for adding numbers with the Paste Special command, watch this short video.
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
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
To see the steps for using Paste Special, and the steps for modifying the Add Numbers macros, watch this video tutorial.
To get 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.
Increase Numbers With Paste Special
Copy VBA Code to Your Workbook
Last updated: July 11, 2021 7:30 PM