| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How fill series containing one variable and one fixed value in XL | Excel Discussion (Misc queries) | |||
| percent of a fixed and variable number | Excel Discussion (Misc queries) | |||
| Fixed column- Variable row # | Excel Discussion (Misc queries) | |||
| Variable column to fixed array | Excel Worksheet Functions | |||
| Replace variable row number in formulas | Excel Discussion (Misc queries) | |||
Linear Mode
