InputBox Code

[Ronbo]

I am trying to create a routine that will update a table by a given user
input. What I have seems to execute but no change are made. Any ideas about
what I am doing wrong would be appreciated.

Option Explicit
Dim rng As Range
Dim lastrow As Long
Dim cell As Range
Dim Amt As Long

Sub GlobalChange()

Amt = InputBox("By What % (As Decimal)")
lastrow = Worksheets("planned sales").Cells(Rows.Count, "d").End(xlUp).Row
Set rng = Range("d8:o" & lastrow)
For Each cell In rng
cell.Value = Round(cell.Value * (1 + Amt), 2)
Next


End Sub

[sebastienm]

Hi,
I am surprised your code doesn make a syntax error even before running, dur
to the Cells(...,"d")
Anyway, try:
Sub test()
Dim rng As Range
Dim lastrow As Long
Dim cell As Range
Dim Amt As Long

Amt = InputBox("By What % (As Decimal)")
lastrow = Worksheets("planned sales").Range("D" & Rows.Count).End(xlUp).Row '
Set rng = Range("d8:o" & lastrow)
For Each cell In rng.cells
cell.Value = Round(cell.Value * (1 + Amt), 2)
Next
End Sub

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Ronbo" wrote:

> I am trying to create a routine that will update a table by a given user
> input. What I have seems to execute but no change are made. Any ideas about
> what I am doing wrong would be appreciated.
>
> Option Explicit
> Dim rng As Range
> Dim lastrow As Long
> Dim cell As Range
> Dim Amt As Long
>
> Sub GlobalChange()
>
> Amt = InputBox("By What % (As Decimal)")
> lastrow = Worksheets("planned sales").Cells(Rows.Count, "d").End(xlUp).Row
> Set rng = Range("d8:o" & lastrow)
> For Each cell In rng
> cell.Value = Round(cell.Value * (1 + Amt), 2)
> Next
>
>
> End Sub
>

[Toppers]

Dim Amt as Double. Declared as Long with input < 1 it will give a value 0...
hence no change to your data.

"Ronbo" wrote:

> I am trying to create a routine that will update a table by a given user
> input. What I have seems to execute but no change are made. Any ideas about
> what I am doing wrong would be appreciated.
>
> Option Explicit
> Dim rng As Range
> Dim lastrow As Long
> Dim cell As Range
> Dim Amt As Long
>
> Sub GlobalChange()
>
> Amt = InputBox("By What % (As Decimal)")
> lastrow = Worksheets("planned sales").Cells(Rows.Count, "d").End(xlUp).Row
> Set rng = Range("d8:o" & lastrow)
> For Each cell In rng
> cell.Value = Round(cell.Value * (1 + Amt), 2)
> Next
>
>
> End Sub
>

[Ronbo]

Thanks to both of you for your help. It was an easy fix by changing long to
double.



"Toppers" wrote:

> Dim Amt as Double. Declared as Long with input < 1 it will give a value 0...
> hence no change to your data.
>
> "Ronbo" wrote:
>
> > I am trying to create a routine that will update a table by a given user
> > input. What I have seems to execute but no change are made. Any ideas about
> > what I am doing wrong would be appreciated.
> >
> > Option Explicit
> > Dim rng As Range
> > Dim lastrow As Long
> > Dim cell As Range
> > Dim Amt As Long
> >
> > Sub GlobalChange()
> >
> > Amt = InputBox("By What % (As Decimal)")
> > lastrow = Worksheets("planned sales").Cells(Rows.Count, "d").End(xlUp).Row
> > Set rng = Range("d8:o" & lastrow)
> > For Each cell In rng
> > cell.Value = Round(cell.Value * (1 + Amt), 2)
> > Next
> >
> >
> > End Sub
> >