ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Replace a fixed number with a variable via popup (https://www.excelbanter.com/excel-worksheet-functions/270895-replace-fixed-number-variable-via-popup.html)

Colin Hayes

Replace a fixed number with a variable via popup
 

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

Gord

Replace a fixed number with a variable via popup
 
Dim num As Long
num = InputBox("enter a number")

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


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


Colin Hayes

Replace a fixed number with a variable via popup
 
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



Gord

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



All times are GMT +1. The time now is 01:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com