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

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

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

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

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

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

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