Subtract same number from a range of cells
How to subtract the same number from a range of cells using Excel and VBA methods
Select the number that you want to subtract > Copy > Select the range that you want to subtract from > Select the Home tab > Clipboard group > Click Paste > Click Paste Special > Select All > Select Subtract > Click OK
| In this example we will subtract the number captured in cell E3 (5) from all of the values in range (B3:C7). This image shows the starting point without any adjustments. |  |
| 1. Select the cell that captures the number that you want to subtract from the range of numbers. 2. Copy the selected cell. Note: in this example we are subtracting 5 from the range of numbers, which is captured in cell E3. To copy the cell you can use the shortcut method by pressing Ctrl + C keys simultaneously. |
 |
| 3. Select the range of cells that captures the numbers that you want to subtract from. Note: in this example we are subtracting from the range of numbers captured in range (B3:C7). |
 |
| 4. Select the Home tab. |  |
| 5. Click on Paste in the Clipboard group. 6. Click on Paste Special. |
 |
| 7.Select the All option in the Paste section. 8. Select the Subtract option in the Operation section. 9. Click OK. |
 |
| This image shows the final outcome of the process explained above. All of the numbers in range (B3:C7) reflect the revised number after having a value of 5 subtracted from them. |  |
|
=B8-$B$5
|
The formula subtracts the number in cell B5 from the value in cell B8. Cell B5 has been treated as an absolute row and column, given we have applied $ signs in front of the row and column reference. This will allow you to drag the formula down to row 12 and column F without adjusting the B5 cell reference, which captures the number that we are subtracting.
|
Sub Subtract_same_number_from_a_range_of_cells()
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim myVal As Range
Dim ws As Worksheet
Dim rng As Range
Dim myVal As Range
Set ws = Worksheets("Analysis")
Set rng = ws.Range("B3:C7")
Set rng = ws.Range("B3:C7")
For Each myVal In rng
myVal = myVal.Value - ws.Range("E3")
Next myVal
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Value Range: If using the same VBA code the range of values that you want to subtract from needs to be captured in range ("B3:C7") in the Analysis worksheet.
Number to subtract: If using the same VBA code the number that you want to subtract from the range of values needs to be captured in cell ("E3") in the Analysis worksheet.
Worksheet Name: Have a worksheet named Analysis.
Value Range: If using the same VBA code the range of values that you want to subtract from needs to be captured in range ("B3:C7") in the Analysis worksheet.
Number to subtract: If using the same VBA code the number that you want to subtract from the range of values needs to be captured in cell ("E3") in the Analysis worksheet.
ADJUSTABLE PARAMETERS
Value Range: Select the range of values that you want to subtract by changing the range reference ("B3:C7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Number to subtract: Select the number that want to subtract from the range of values by changing the value in cell ("E3") or changing the cell reference ("E3"), in the VBA code, to any cell in the worksheet that captures the number that you want to subtract and doesn't conflict with formula.
Value Range: Select the range of values that you want to subtract by changing the range reference ("B3:C7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Number to subtract: Select the number that want to subtract from the range of values by changing the value in cell ("E3") or changing the cell reference ("E3"), in the VBA code, to any cell in the worksheet that captures the number that you want to subtract and doesn't conflict with formula.
ADDITIONAL NOTES
Note 1: The VBA code returns the adjusted numbers in the same range where the numbers that you are subtract from are captured.
EXPLANATION
This tutorial explains and provides step by step instructions on how to subtract the same number from a range of cells using Excel and VBA methods.
This tutorial explains and provides step by step instructions on how to subtract the same number from a range of cells using Excel and VBA methods.
Excel Methods: Using Excel you can subtract from a range of cells by the same number using either the paste special option or a formula. Using the paste special approach the existing values that you want to subtract from will be replaced with the revised numbers. Using the formula approach the results will need to be presented in a different range. In this example, using the formula method, the results are captured in range E8:F12.
VBA Method: The VBA method in this tutorial shows how to automate this process. The results for this example replace the existing numbers that you are subtracting from. This derives with the same result as the Excel (paste special) method.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Add same number to a range of cells | How to add the same number to a range of cells using Excel and VBA methods | |
| Multiply a range of cells by same number | How to multiply a range of cells by the same number using Excel and VBA methods | |
| Divide a range of cells by same number | How to divide a range of cells by the same number using Excel and VBA methods |