View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Gord is offline
external usenet poster
 
Posts: 86
Default Replace a fixed number with a variable via popup

Happy to assist.

Gord

On Tue, 16 Aug 2011 20:01:02 +0100, Colin Hayes
wrote:

In article , Gord
writes
Dim num As Long
num = InputBox("enter a number")

ActiveCell.FormulaR1C1 = "=RC[-1]*" & num




HI Gord

OK that's perfect. Thanks for your help.



Best Wishes ,




Gord Dibben Microsoft Excel MVP

On Tue, 16 Aug 2011 18:15:28 +0100, Colin Hayes
wrote:


Hi All

In the code below , can somebody suggest how to replace the fixed
multiplication by 1.4 with a variable inserted via popup?


Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1.4"
Columns("N:N").Select
Selection.NumberFormat = "0.00"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N" & lrow),
Type:=xlFillDefault
Range("N2:N" & lrow).Select
Range("M1").Select
Selection.Cut Destination:=Range("N1")
Columns("N:N").Select
Selection.Copy
Columns("M:M").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("M:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0.00"
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft


Grateful for any advice.



Best Wishes