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.

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.

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.

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

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

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

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

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