Code Help

  • Thread starter Thread starter Ronbo
  • Start date Start date
R

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
 
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
 
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
 
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
Click to expand...

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
 
It still gives me "False" in each cell.



OssieMac said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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
Click to expand...

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
Click to expand...
 
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
 
Back
Top