Worksheet change Macro Question

[Vick]

I believe I need a worksheet change macro for this, I can't think of any
other way of doing it. I have one column that looks like this: The rates are
all static numbers no formulas. What I want to be able to do is that whenever
someone changes the 0% cell or A1, that the macro will increase the rates by
that percentage. For example if I were to cahnge the % to 10% the rate 110
would change to 121. Thanks for any help you can give.

0%
Bill Rate
110
140
160
200
150
160
200
160
180
140
150
100
140
350
100

Vick

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit
Dim cell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For Each cell In Me.Range(Target.Offset(1, 0),
Target.End(xlDown))

If IsNumeric(cell.Value) Then

cell.Value = cell.Value * (1 + .Value)
End If
Next cell
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Vick]

That worked great, just had one more question. I have another column in that
same spreadsheet I'd like to do the same thing too. So I have two lists, one
in A and one in B with that % at the top. I'd like to change B1 and have it
change Column B, and the same with A1 and A. Is that possible?

Thanks

 

[Gord Dibben]

You want this done in place?

Easily done with or without event code but you must realize you would have no
paper or audit trail in case of errors made in entry in A1.

You would be safer having a formula in column B that referred to A1

=A3 + (A3*$A$1)/100

You can then change the value in A1 and the values in column B will reflect that
without changing the base values in column A

To change all in place just enter 1.1 in an empty cell then copy.

Select the numbers and Paste Special>Multiply>OK>Esc.

Delete the 1.1 from the cell.


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1,B1" '<== change to suit
Dim cell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For Each cell In Me.Range(Target.Offset(1, 0), _
Target.End(xlDown))

If IsNumeric(cell.Value) Then

cell.Value = cell.Value * (1 + .Value)
End If
Next cell
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Vick]

Thank you for the idea Gord, but unfortunately that won't work. What I'm
trying to is allow the user to change certain areas by two methods rather
than one. So that they can change rate for example by a % or change it by
typing in the cell. And if they type in the cell, they can still do a % later
if they want. If they were to type in the cell the formula disappears and
they can no longer change by the %. This is for a plan so, I'm not concerned
with an audit trail at this point.

Thanks

 

[mohavv]

I believe I need a worksheet change macro for this, I can't think of any
other way of doing it.  I have one column that looks like this: The rates are
all static numbers no formulas. What I want to be able to do is that whenever
someone changes the 0% cell or A1, that the macro will increase the rates by
that percentage. For example if I were to cahnge the % to 10% the rate 110
would change to 121. Thanks for any help you can give.

0%
Bill Rate
110
140
160
200
150
160
200
160
180
140
150
100
140
350
100

Vick

You could do it like this (example cell A3):

=110*(1+A$1)

Cheers,

Harold

 

[Bob Phillips]

If an audit trail were required Gord, it could be easily added.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gord Dibben]

There is no formula in A1 where the % is entered so don't know what you are
getting at.

The only formulas are in column B


Gord

 

[Vick]

I added some code below what you supplied to say it it zero, copy in some
other data. The trouble I'm having is that if hit the arrow key instead of
return. The wrong column gets copied over.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "L32,H32,F32" '<== change to suit
Dim cell As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
For Each cell In Me.Range(Target.Offset(1, 0), Target.End(xlDown))

If IsNumeric(cell.Value) Then

cell.Value = cell.Value * (1 + .Value)
End If
Next cell
End With
End If

If Intersect(Target, Me.Range(WS_RANGE)) = 0 Then
With Target

ActiveCell.Offset(-29, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(30, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With

End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

That code looks wrong to me, but tell me in words what you are trying to do
if the target cell is zero?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Vick]

When L32,H32, or F32 change to zero. Go up and copy the orginal data from
above and paste values over the data below. The idea here is that if they
make the cell 0 they can start over from where they were at the begining.
Thus erasing the % change they did earlier.