Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
I want to be able to add what ever number has been subtracted from one colum
to another colum. Example: colum A colum B 210 25 200 35 190 45 250 45 230 65 200 95 When I change the number in colum A to a lower number. I want it to automatically add to colum B. But if I add to colum A, I want nothing to happen. Colum A is our in stock amount. Colum B is total used or sold. This is probably a simple thing to do, but so far I have not found the formula, or macro to do it. I Thank You for any Help you may tell me. Thank You, Craig Alan Johnson Sr. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
Option Explicit
Private prev As Variant Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < prev Then .Offset(0, 1).Value = _ .Offset(0, 1).Value + (prev - .Value) End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) prev = Target.Value 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) "craigtab" wrote in message ... I want to be able to add what ever number has been subtracted from one colum to another colum. Example: colum A colum B 210 25 200 35 190 45 250 45 230 65 200 95 When I change the number in colum A to a lower number. I want it to automatically add to colum B. But if I add to colum A, I want nothing to happen. Colum A is our in stock amount. Colum B is total used or sold. This is probably a simple thing to do, but so far I have not found the formula, or macro to do it. I Thank You for any Help you may tell me. Thank You, Craig Alan Johnson Sr. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
Hi Bob,
I tried what you said, but I kept getting error messages Thank You -- Craig Alan Johnson Sr. "Bob Phillips" wrote: Option Explicit Private prev As Variant Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:A" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < prev Then .Offset(0, 1).Value = _ .Offset(0, 1).Value + (prev - .Value) End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) prev = Target.Value 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) "craigtab" wrote in message ... I want to be able to add what ever number has been subtracted from one colum to another colum. Example: colum A colum B 210 25 200 35 190 45 250 45 230 65 200 95 When I change the number in colum A to a lower number. I want it to automatically add to colum B. But if I add to colum A, I want nothing to happen. Colum A is our in stock amount. Colum B is total used or sold. This is probably a simple thing to do, but so far I have not found the formula, or macro to do it. I Thank You for any Help you may tell me. Thank You, Craig Alan Johnson Sr. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
And those messages would be.........?
Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 6 Jun 2008 15:58:02 -0700, craigtab wrote: Hi Bob, I tried what you said, but I kept getting error messages Thank You |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
Do I need to change any values to make it work?
My colums are "C" and "I" do I need to ref. them somewhere in the code? Thank You, -- Craig Alan Johnson Sr. "Gord Dibben" wrote: And those messages would be.........? Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 6 Jun 2008 15:58:02 -0700, craigtab wrote: Hi Bob, I tried what you said, but I kept getting error messages Thank You |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
I did finally get it to work. Thank you very much. What if you have colums
spaced every other one? Would a simple change in the program have to be made, or would it have to be re-written? I am sure you can tell I am new at this. Thank you for your help! -- Craig Alan Johnson Sr. "Gord Dibben" wrote: And those messages would be.........? Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 6 Jun 2008 15:58:02 -0700, craigtab wrote: Hi Bob, I tried what you said, but I kept getting error messages Thank You |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
This will do B,D,F,H,J,L columns
Option Explicit Private prev As Variant Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:M" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If .Column Mod 2 = 1 Then With Target If .Value < prev Then .Offset(0, 1).Value = _ .Offset(0, 1).Value + (prev - .Value) End With End If End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) prev = Target.Value End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "craigtab" wrote in message ... I did finally get it to work. Thank you very much. What if you have colums spaced every other one? Would a simple change in the program have to be made, or would it have to be re-written? I am sure you can tell I am new at this. Thank you for your help! -- Craig Alan Johnson Sr. "Gord Dibben" wrote: And those messages would be.........? Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 6 Jun 2008 15:58:02 -0700, craigtab wrote: Hi Bob, I tried what you said, but I kept getting error messages Thank You |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 Formula or Macro
Thank You Bob for all of your help!
Take Care, -- Craig Alan Johnson Sr. "Bob Phillips" wrote: This will do B,D,F,H,J,L columns Option Explicit Private prev As Variant Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A:M" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If .Column Mod 2 = 1 Then With Target If .Value < prev Then .Offset(0, 1).Value = _ .Offset(0, 1).Value + (prev - .Value) End With End If End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) prev = Target.Value End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "craigtab" wrote in message ... I did finally get it to work. Thank you very much. What if you have colums spaced every other one? Would a simple change in the program have to be made, or would it have to be re-written? I am sure you can tell I am new at this. Thank you for your help! -- Craig Alan Johnson Sr. "Gord Dibben" wrote: And those messages would be.........? Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 6 Jun 2008 15:58:02 -0700, craigtab wrote: Hi Bob, I tried what you said, but I kept getting error messages Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I record a macro in Excel 2000? | Excel Discussion (Misc queries) | |||
How do you run a Macro from an IF statement in Excel 2000? | Excel Worksheet Functions | |||
Macro created in Excel 2000 SR1 won't run with SP3 | Excel Discussion (Misc queries) | |||
Macro in Excel 2000 | Excel Discussion (Misc queries) | |||
Macro in Excel 2000 | Excel Discussion (Misc queries) |