VBA Express : Excel - Paste Special: Multiply

Excel

Paste Special: Multiply - User Defined

Ease of Use

Easy

Version tested with

2000

Submitted by:

Zack Barresse

Description:

A user-defined amount to Paste Special: Multiply on the entire selection.

Discussion:

Often when you need to multiply an entire selection by the same amount, such as reducing all your amounts by 10%, you would need to find a blank cell, type in a number (0.9), copy it, re-select your range, Paste Special: Multiply. With this macro, you don't need to do that anymore. While it wouldn't be great if you only do this once in a while, it'll be terrific if you do this often! Just enter the multiplier amount and let the code handle the rest.

Code:

instructions for use

			Option Explicit 
 
Sub psMultiply() 
     
    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 selection multiplier:", _ 
    Title:="Selection multiplier", 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:=xlMultiply 
        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 psMultiply.

Sample File:

psMultiply.zip 7.84KB

Approved by mdmackillop

This entry has been viewed 185 times.