VBA Express : Excel - Paste Special: Subtract

Excel

Paste Special: Subtract - User Defined

Ease of Use

Intermediate

Version tested with

2000/XP

Submitted by:

Zack Barresse

Description:

A user-defined amount to Paste Special: Subtract on an entire selection.

Discussion:

Often when you need to subtract an entire selection of formulas by the same amount (e.g. subtract 1500) you would need to find a blank cell, type in a number (e.g. 1500), copy it, re-select your range, Paste Special: Subtract. With this you need not do any of that. Just enter the amount to subtract and let the code handle the rest.

Code:

instructions for use

			Option Explicit 
Sub psSubtract() 
     
    Dim y As Integer 'The multiplier value, user-defined
    Dim x As Range 'Just a blank cell for variable
    Dim z As Range 'Selection to work with
    Set z = Selection 
    y = Application.InputBox("Enter amount to subtract from selection:", _ 
    Title:="Subtract from selection", Default:=10, Type:=1) 
    Set x = Range("A65536").End(xlUp).Offset(1) 
    If y = 0 Then Exit Sub 'Cancel button will = 0, hence cancel
    If x <> "" Then 
        Exit Sub 
    Else: x.Value = y 
        x.Copy 
        z.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract 
        Application.CutCopyMode = False 'Kill copy mode
    End If 
    x.ClearContents 'Back to normal : )
     
End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Tools-Macro-Macros, and double-click psSubtract

Sample File:

psSubtract.zip 7.38KB

Approved by mdmackillop

This entry has been viewed 113 times.