subtraction
 

im supposed to write something for work and i have just started this.
we have spreadsheets with part numbers, and how many we have for eac
one. after each total we have how many people want to buy. right no
we just do the math manually. heres an example:

in cell b2 is the total number of parts we have, say its 300. afte
that in c2 to like t2 we have buyers with an amount they want.
subtract each from the total we have. so we start out like this, d2
c2 , e2 - d2. adn we keep subtracting what the buyers want from what w
have in stock until we dont have anything left. when we run out, w
know who we need to ship to , and who is oging to be waiting till w
make new parts. so basically subtract what each buyers wants from th
total we have till it hits a negative number, the last positive number
is the last date we will be shipping out the parts. and we have lik
300 of these. im supposed to write something to automatically subtrc
all these numbers of the buyers from the total parts we have. anyon
understand? its basically subtraction down the spreadsheet. until i
hits a negative. but it has to do it in each row. and theres like 30
rows. so manually takes hours, if we could write soemthing to do it fo
us, that would help. anyone have a clue? im not asking anyone to rit
it for me, but can someone kinda help me on my way? cause im still s
new. and stuck. any suggestions will help,. i appreciate it.

-dere

--
Message posted from http://www.ExcelForum.com

subtraction
 

Put the following code in a button and it will highlight the orders yo
can fill i yellow. Changing the ColorIndex will give you differen
colors if you want, but let me know if you want it to mark them in
different way or if you need instructions for creating the button.
Note I used "Sheet1" as the sheet name. Change this if necessary.
Please let me know if you have any problems.

Private Sub CommandButton1_Click()
lastRow = Worksheets("Sheet1").UsedRange.Row - 1 + _
Worksheets("Sheet1").UsedRange.Rows.Count
lastCol = Worksheets("Sheet1").UsedRange.Column - 1 + _
Worksheets("Sheet1").UsedRange.Columns.Count
With Worksheets("Sheet1")
For x = 2 To lastRow
On Error GoTo Skip
y = 2
runTot = 0
parts = .Cells(x, 2).Value
Do Until y = lastCol _
Or runTot parts
y = y + 1
runTot = runTot + .Cells(x, y).Value
Loop
If runTot parts Then
For z = 3 To y - 1
.Cells(x, z).Interior.ColorIndex = 6
Next z
ElseIf y = lastCol Then
For z = 3 To y
If .Cells(x, z).Value < "" Then
.Cells(x, z).Interior.ColorIndex = 6
End If
Next z
End If
Skip:
Next x
End With
End Sub

- Piku

--
Message posted from http://www.ExcelForum.com

subtraction
 

ok, let me try this out, i appreciate it

--
Message posted from http://www.ExcelForum.com

subtraction
 

i get a runtime error 13 on

runTot = runTot + .Cells(x, y).Valu

--
Message posted from http://www.ExcelForum.com

subtraction
 

i had a premade button for this, and i also changed the sheet1 to wha
it needed to be? everything still look good?

Private Sub launchbutton_1_Click()
lastRow = Worksheets("First Sheet").UsedRange.Row - 1 + _
Worksheets("First Sheet").UsedRange.Rows.Count
lastCol = Worksheets("First Sheet").UsedRange.Column - 1 + _
Worksheets("First Sheet").UsedRange.Columns.Count
With Worksheets("First Sheet")
For x = 2 To lastRow
On Error GoTo Skip
y = 2
runTot = 0
parts = .Cells(x, 2).Value
Do Until y = lastCol _
Or runTot parts
y = y + 1
runTot = runTot + .Cells(x, y).Value
Loop
If runTot parts Then
For z = 3 To y - 1
.Cells(x, z).Interior.ColorIndex = 6
Next z
ElseIf y = lastCol Then
For z = 3 To y
If .Cells(x, z).Value < "" Then
.Cells(x, z).Interior.ColorIndex = 6
End If
Next z
End If
Skip:
Next x
End With
End Sub


also, the number of parts we have onhand starts on g4, if that matters

--
Message posted from http://www.ExcelForum.com

subtraction
 

Everything looks straight, but yeah, that's important. What is th
exact layout of the sheet. I was running under the assumption that th
number of parts available was in column B and the list started on ro
2. I'll tell you what, if you send me the file, you can put in dumm
information for anything private, I'll fix it up and send it back.
I'll send you my address... - Piku

--
Message posted from http://www.ExcelForum.com