Worksheet change Macro Question
 

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

Worksheet change Macro Question
 

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" wrote in message
...
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

Worksheet change Macro Question
 

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

"Bob Phillips" wrote:

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" wrote in message
...
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

Worksheet change Macro Question
 

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 SpecialMultiplyOKEsc.

Delete the 1.1 from the cell.


Gord Dibben MS Excel MVP

On Mon, 28 Jan 2008 14:13:02 -0800, Vick wrote:

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

Worksheet change Macro Question
 

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" wrote in message
...
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

"Bob Phillips" wrote:

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" wrote in message
...
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

Worksheet change Macro Question
 

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

"Gord Dibben" wrote:

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 SpecialMultiplyOKEsc.

Delete the 1.1 from the cell.


Gord Dibben MS Excel MVP

On Mon, 28 Jan 2008 14:13:02 -0800, Vick wrote:

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

Worksheet change Macro Question
 

On Jan 29, 11:13*am, Vick wrote:
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

Worksheet change Macro Question
 

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" <gorddibbATshawDOTca wrote in message
...
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 SpecialMultiplyOKEsc.

Delete the 1.1 from the cell.


Gord Dibben MS Excel MVP

On Mon, 28 Jan 2008 14:13:02 -0800, Vick
wrote:

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

Worksheet change Macro Question
 

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

On Mon, 28 Jan 2008 15:23:01 -0800, Vick wrote:

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 %.

Worksheet change Macro Question
 

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" wrote:

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" wrote in message
...
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

"Bob Phillips" wrote:

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" wrote in message
...
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

Worksheet change Macro Question
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

"Bob Phillips" wrote:

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" wrote in message
...
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

Worksheet change Macro Question
 

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.

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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

"Bob Phillips" wrote:

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" wrote in message
...
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