Inventory

[himanshuvrat]

I want to shift quantity of ‘Items’ from worksheet “Invoice” to Worksheet “Purchase” in column Named as ‘Balance Quantity’ where it will be deducted from initially available quantity in corresponding row to the ‘Item’ of sale. This quantity of ‘Item’ will also be transferred to in column Named as ‘Total Sale’ in corresponding row to the invoiced ‘Item’, where it will be added every time when this item will be invoiced and invoice printed.

The ‘Item Names’ in worksheet “Purchase” is in Column “A” from “A2”which has nearly 770 items. The ‘Balance quantity’ is shown in Column “G”. The ‘Total Sales’ is shown in column “I”

The ‘Item Names’ Work Sheet “Invoice“ has in Column “A” from “A12 to A30” . The Number of Items may vary from 1 to 21. Quantity of Items is in Column “B” from “B12” to “B30”

The VBA code for the above I have written is as follows but it did not run.

Sub UpdateInventory()
Dim x As Long
Dim r As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim newQuantity As Long

Set ws1 = Worksheets("Invoice")
Set ws2 = Worksheets("purchase")

x = 12
r = 3
Do Until x = 30
    Do Until ws2.Range("A" & r).Value = ""
     If ws1.Range("C" & x).Value = ws2.Range("A" & r).Value Then
    newQuantity = ws2.Range("G" & r).Value - ws1.Range("E" & x).Value
    ws2.Range("G" & r).Value = newQuantity
    newQuant = ws2.Range("I" & r).Value + ws1.Range("E" & x).Value
    ws2.Range("I" & r).Value = newQuant  
     x = x + 1
     Else
     r = r + 1
     End If
    Loop
x = x + 1
Loop
End Sub

Please help.

 

[NARAYANK991]

Hi ,

You are incrementing x in two places.

I have not gone through the logic to find out when this can happen , but if this happens when x has the value 29 , then x will become 31 , and thereafter will never satisfy the test of the outer Do While loop.

For such reasons , never use equality as a test for exiting a loop ; always use either <= or >= or <> as the tests.

Narayan