The macros below do what you want. The first macro is an event macro that
fires whenever an entry is made in Column B. This macro must be placed in
the sheet module of your sheet. To access that module, right-click on the
sheet tab and select View Code. Paste this first macro into that module,
"X" out of the module to return to your sheet. The other 2 macros including
everything from "Option Explicit" and below should be placed in a regular
module. Come back if you need more. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 2 Then
If Target.Row = 2 Then _
Call FirstEntry(Target)
If Target.Row 2 Then _
Call TheRestEntry(Target)
End If
End Sub

Option Explicit
Dim c As Long
Dim WhatsLeft As Long

Sub FirstEntry(TheCell As Range)
Dim cc As Long
Application.EnableEvents = False
cc = Int(TheCell / 1000)
For c = 0 To cc - 1
TheCell.Offset(c, 3) = 1000
Next c
WhatsLeft = TheCell.Value - cc * 1000
TheCell.Offset(cc, 3) = WhatsLeft
Application.EnableEvents = True
End Sub

Sub TheRestEntry(TheCell As Range)
Application.EnableEvents = False
TheCell.Offset(, 1) = TheCell.Offset(-1, 3)
TheCell.Offset(, 2) = 1000 - TheCell.Offset(, 1)
TheCell.Offset(, 3) = 1000
WhatsLeft = TheCell - TheCell.Offset(, 2)
For c = 1 To 100
If WhatsLeft = 1000 Then
TheCell.Offset(c, 3) = 1000
WhatsLeft = WhatsLeft - 1000
Else
TheCell.Offset(c, 3) = WhatsLeft
Exit For
End If
Next c
Application.EnableEvents = True
End Sub


"Bowmanator" wrote in message
...
forgot, i'm using office xp pro

"Bowmanator" wrote:

you assumed everything correct, and everything entered in b2 and below
will
be greater then 1,000.

"Otto Moehrbach" wrote:

You didn't say anything about what Excel should/could use as the
trigger to
fire the automation. For now I'll assume it's any numerical entry made
in
Column B below row 1. Excel can do lots for you with this but it must
be
told every little detail so I have a question.
Question: Is every entry in Column B ALWAYS 1000 or greater? If not,
and
the sum of that entry and the "leftover" (53 in your 3/10 example) is
less
than 1000, what do you want to happen?
Another question: What version of Excel are you using? Otto

"Bowmanator" wrote in message
...

what i'm doing here is trying to keep track of how many full truck
loads i
can ship before i run out of product that was ran on a certain day.
then
the
next load will be a split load.

ex: on 3/10 we ran 2053 cases, that allows me to ship 2 full loads of
that
days run leaving 53 cases left,
on 3/11 we run 1019 so in order to ship a full load of 1,000 i'll
need the
53 cases left over from 3/10 plus 947 caes from 3/11 to equal 1000.
then
it
shows a balance of 72 let over from 3/11

hope this sounds better

thanks
"Otto Moehrbach" wrote:

What is the sequence of events? You say you want to automate this.
What
comes first? Detail what you enter and then what you want Excel to
do
(the
automate part). Then what happens next? The next date row maybe?
In short, Excel is dumb and it needs to know what is the trigger to
make
it
automate something. Maybe any entry in Column B? It might help if
you
provide the series of steps you must do when you do this manually.
Remember
that you are talking to people who know nothing about what you have
or
want
to have. HTH Otto

"Bowmanator" wrote in message
...
i'm working on a shipping log that i do now by hand and would like
to
automate if possible. every load equals 1,000 and alot of the
loads are
split
loads.

a b c d
e
1 date amount previous current total
2 3/10 2053
1000
3
1000
4
53
5 3/11 1019 53 947 1000
6
72
7 3/12 3000 72 928 1000
8
1000
9
1000
10
72

in this example it shows what i need to equal 1,000 and if there's
over
1,000 left of that run i'd like the next line to show 1,000 and so
on
until i
can't make a full load of 1,000.

hope this sounds right
thanks

.

.