InputBox Code
 

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

InputBox Code
 

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

InputBox Code
 

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

InputBox Code
 

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