Adds an amount (or subtract) from a range of cells in one click
Similar to Cell_Add_or_Subtract but works on all cells in selection.
Can pass range as string to apply, if not passed, current selection of cells will be modified.
Works on activesheet for now, in future, we may have a version that works on other sheets/workbooks.
Sub Range_Add_or_Subtract(Amount, Optional Plus1_or_Minus2 = 1, Optional ToRange = "Active")
' Adds amount to all cells in range, or subtract
' Pass Plus1_or_Minus2 as 1 (or ignore) to make it addition, 2 to make it subtraction.
' Only applies for cells with a constant numeric values (Not formula)
' applies to active sheet only
If ToRange = "Active" Then ToRange = Selection.Address
If Not IsNumeric(Amount) Then Exit Sub
Amount2Add = Amount
If Plus1_or_Minus2 = 2 Then Amount2Add = 0 - Amount
For Each Cee In Range(ToRange)
If Left(Cee.Formula, 1) < > "=" And IsNumeric(Cee.Value) Then
Cee.Value = Cee.Value + Amount2Add
End If
End If
End Sub
' Adds amount to all cells in range, or subtract
' Pass Plus1_or_Minus2 as 1 (or ignore) to make it addition, 2 to make it subtraction.
' Only applies for cells with a constant numeric values (Not formula)
' applies to active sheet only
If ToRange = "Active" Then ToRange = Selection.Address
If Not IsNumeric(Amount) Then Exit Sub
Amount2Add = Amount
If Plus1_or_Minus2 = 2 Then Amount2Add = 0 - Amount
For Each Cee In Range(ToRange)
If Left(Cee.Formula, 1) < > "=" And IsNumeric(Cee.Value) Then
Cee.Value = Cee.Value + Amount2Add
End If
End If
End Sub
Amount, Optional Plus1_or_Minus2 = 1, Optional ToRange = "Active"
Views 120
Downloads 41
CodeID
DB ID