Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 %. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to name worksheet tabs using a cell within the worksheet? | Excel Discussion (Misc queries) | |||
Running of Worksheet Change Macro breaks undo functionality. | Excel Discussion (Misc queries) | |||
Using this Automatic Resizing Macro with Worksheet Change | Excel Discussion (Misc queries) | |||
Excel Formula/Worksheet maybe Macro Question | Excel Worksheet Functions | |||
How do I change macro text with another macro? | Excel Discussion (Misc queries) |