Range_Add_or_Subtract

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.

CodeFunctionName
What is this?

Public

Tested

Original Work
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

Amount, Optional Plus1_or_Minus2 = 1, Optional ToRange = "Active"

Views 89

Downloads 31

CodeID
DB ID

ANmarAmdeen
604
Attachments
Revisions

v1.0

Saturday
February
18
2023