Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 16th 11, 06:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 465
Default 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   Report Post  
Old August 16th 11, 06:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 86
Default 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   Report Post  
Old August 16th 11, 08:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 465
Default 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   Report Post  
Old August 16th 11, 08:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How fill series containing one variable and one fixed value in XL nomad Excel Discussion (Misc queries) 2 September 18th 09 09:25 PM
percent of a fixed and variable number Lorne Excel Discussion (Misc queries) 7 June 8th 08 08:43 PM
Fixed column- Variable row # Vasilis Tergen Excel Discussion (Misc queries) 3 December 2nd 06 06:10 PM
Variable column to fixed array asaylor Excel Worksheet Functions 0 August 9th 06 05:28 PM
Replace variable row number in formulas Ldub Excel Discussion (Misc queries) 0 November 3rd 05 10:25 PM


All times are GMT +1. The time now is 10:17 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017