Hi ,

have a small dilema. I have written some code that i thought would keep a updated running balance from a bottle. here is my code:

Sub cmdInventory_Click()

' calculates and shows the inventory report
Dim sBottleID As String ' variable to hold current bottle id
Dim i As Integer
Dim sgBalance As Single ' variable to hold the available balance of the bottle
Dim iRow As Integer
Dim sIsotope As String
On Error Resume Next
For iRow = 4 To 600
' Loop through the bottle spreadsheet
If Sheets("Main Database").Cells(iRow, 1) = "" Then Exit For ' End of File
sBottleID = Sheets("Main Database").Cells(iRow, 2)
sgBalance = Sheets("Main Database").Cells(iRow, 6)
' Loop through the used amounts and subtract from balance for bottle
With Sheets("Used Amount Database")
For i = 4 To 600
If .Cells(i, 1) = sBottleID Then
sgBalance = sgBalance - .Cells(i, 7)
ElseIf .Cells(i, 1) = "" Then
Exit For
End If
Next
End With
' Update the balances on the main sheet
Sheets("Main Database").Cells(iRow, 14) = sgBalance
Sheets("Main Database").Cells(iRow, 15) = DateDiff("d", Date, Sheets("Main Database").Cells(iRow, 5))
sIsotope = Sheets("Main Database").Cells(iRow, 11)

My problem is that although it works, it will only work once. What I mean is that it will update only once. If I were to take more from the bottle,it just remembers the first time value. Can some one help me with this. I know I am close.

chris