Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
I have the following code that I need to take a range (f16..f51) and
increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cell In rng cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. What am I doing wrong? Ronbo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
Hi Ronbo,
I prefer to use cel in lieu of cell so as not to be confused with reserved words. Option Explicit Dim rng As Range Dim lastrow As Long Dim cel As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cel In rng cel.FormulaR1C1 = "=RC[-1]*(1+" & Amt & ")" Next cel End Sub -- Regards, OssieMac "Ronbo" wrote: I have the following code that I need to take a range (f16..f51) and increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cell In rng cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. What am I doing wrong? Ronbo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
This line: cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),")
reads: cell.Value = (this = that) so it is not surprising the result is either TRUE or FALSE Sub Test() Amt = InputBox("By What % (As Decimal)") For Each J = 16 to 51 Cells(J, "F").Value = Cells( J , "E") *(1+ Amt) Next J ,,,, no time to test but I think you get the idea best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ronbo" wrote in message ... I have the following code that I need to take a range (f16..f51) and increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cell In rng cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. What am I doing wrong? Ronbo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
On Feb 17, 6:45*pm, Ronbo wrote:
I have the following code that I need to take a range (f16..f51) and increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") * * Set rng = Range("f16:f51") * * For Each cell In rng * * cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. *What am I doing wrong? Ronbo Give this a try Sub Test() Dim amt As Variant Dim rng As Range, cell As Range amt = InputBox("By What % (As Decimal)") Set rng = Range("F16:F51") For Each cell In rng.Cells cell = cell.Offset(0, -1) * 1+amt Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
It still gives me "False" in each cell.
"OssieMac" wrote: Hi Ronbo, I prefer to use cel in lieu of cell so as not to be confused with reserved words. Option Explicit Dim rng As Range Dim lastrow As Long Dim cel As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cel In rng cel.FormulaR1C1 = "=RC[-1]*(1+" & Amt & ")" Next cel End Sub -- Regards, OssieMac "Ronbo" wrote: I have the following code that I need to take a range (f16..f51) and increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cell In rng cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. What am I doing wrong? Ronbo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
Thanks a lot for all of the help. After including all of the code OssieMac
provided rather than just changing the cell to cel... it (seems) to work perfectly. Thanks. Regrads Ronbo "CurlyDave" wrote: On Feb 17, 6:45 pm, Ronbo wrote: I have the following code that I need to take a range (f16..f51) and increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cell In rng cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. What am I doing wrong? Ronbo Give this a try Sub Test() Dim amt As Variant Dim rng As Range, cell As Range amt = InputBox("By What % (As Decimal)") Set rng = Range("F16:F51") For Each cell In rng.Cells cell = cell.Offset(0, -1) * 1+amt Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Help
hi
might as well add my 2 cents worth. in your original code, you have the variable Amt inside the double quotes leading VB to assume that you ment to multiply by Amt literally and not the variable entered into the input box. i move the variable amt outside the double quotes. also may several other adjustments. Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Amt = InputBox("By What % (As Decimal)") Amt = Amt + 1 Set rng = Range("f16:f51") For Each cell In rng cell.FormulaR1C1 = "=RC[-1]*" & Amt Next cell "Ronbo" wrote: I have the following code that I need to take a range (f16..f51) and increase/decrease it by a user input value i.e. f16=e16*(1+user input) Option Explicit Dim rng As Range Dim lastrow As Long Dim cell As Range Dim Amt As Double Sub Test() Amt = InputBox("By What % (As Decimal)") Set rng = Range("f16:f51") For Each cell In rng cell.Value = (cell.FormulaR1C1 = "=RC[-1]*(1+Amt),") I get "FALSE" in each cell. What am I doing wrong? Ronbo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |