Home > Skills > Data Entry > Paste Special Add Number to Range of CellsIn Microsoft Excel, see the easiest way to add a specific amount to the numbers in a range of cells, either manually, or with a macro, no formulas required! Tip: For other ways to get a sum of values, go to the sum function examples page, for written steps, videos and sample files. |
How to Add Amounts to Cell ValuesTo increase the amounts in a range of cells -- adding the same amount to each cell -- you can use see the steps below. There is a simple example shown below, and you can see other ways to change cell values with on the Paste Special command page --add, subtract, multiply, and divide. Increase Cell Values with Paste SpecialIn this example, there are test scores in the worksheet cells A2:B6. All of the scores need to be increased by 10. To increase the amounts by 10, follow these steps
|
Paste Special Dialog BoxIn the Paste Special dialog box, follow these steps:
On the worksheet, all numbers in the selected cells have increased by 10 Then, to clean up the worksheet, clear the cell where you typed the 10 |
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 SpecialTo see the steps for using Paste Special, and the steps for modifying the Add Numbers macros, watch this video tutorial. |
Get the Sample FileTo 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. |
More Tutorials |
Last updated: December 19, 2022 9:15 PM