Updating 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 of invoiced item in corresponding row to the ‘Item’ of sale. This quantity of ‘Item’ will also be transferred to Worksheet “Purchase” in column Named as ‘Total Sale’ in corresponding row to the invoiced ‘Item’, where it will be added every time when this item is invoiced and invoice is printed.
The ‘Item Names’ in worksheet “Purchase” is in Column “A” starting from “A2”which has nearly 770 items. The ‘Balance quantity’ is shown in Column “H”. The ‘Total Sales’ is shown in column “I”
The ‘Item Names’ in Work Sheet “Invoice“ is in Column “A” from “A12 to A30” . The invoice may contain any number of items ranging from minimum 1 to maximum 21 , out of all the 770 items contained in worksheet “Purchase. 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("H" & r).Value - ws1.Range("E" & x).Value
ws2.Range("H" & 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.