Code Help

[Ronbo]

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

 

[OssieMac]

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

 

[Bernard Liengme]

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

 

[CurlyDave]

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

 

[Ronbo]

It still gives me "False" in each cell.

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

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

 

[Ronbo]

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

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

 

[FSt1]

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