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.
|
Option Explicit
Sub psMultiply()
Dim y As Integer
Dim x As Range
Dim z As Range
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
If x <> "" Then
Exit Sub
Else: x.Value = y
x.Copy
z.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Application.CutCopyMode = False
End If
x.ClearContents
End Sub
|