Invoice to Inventory

[Trevor Kaltenbrunn]

Hi All

I have written a code that i cant get working, Please can someone help?

This code is for automating invoice to inventory so that when the invoice has a quantity of a certain item it will subtract from the stock. If i run the code it works on the first line to subtract the correct value entered into the invoice off the inventory.

Sheet 1 is my invoice and the stock code is entered into Row B16 to B26 or last row (The invoice is limited to 10 rows)
Sheet 2 is the inventory list and the stock is entered from A2 to last row. (The inventory could expand in rows as new inventory is added.

Please see below code, Please can you give me assistance.

Sub updateInventorys()'we declare 4 variables
Dim rng1, rng2, cell1, cell2 As Range
Dim lastRow1 As Long
lastRow1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = Worksheets("Sheet1").Range("B16,B" & lastRow1)
Dim lastRow2 As Long
lastRow2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Worksheets("Sheet2").Range("A2,A" & lastRow2)


For Each cell1 In rng1
If IsEmpty(cell1.Value) Then Exit For


For Each cell2 In rng2
If IsEmpty(cell2.Value) Then Exit For


If cell1 = cell2 Then
cell2.Offset(0, 1) = cell2.Offset(0, 1) - cell1.Offset(0, 2)


End If
Next cell2
Next cell1


End Sub

 

[igold]

Hi Trevor,

Welcome to the MrExcel Forum.

What is wrong. Does the code not do what you want, or is it throwing an error. If it is an error, can you tell us what the error is and what line of code triggers it.

Additionally, as an FYI, when you declare your variables, you must declare what each one is separately. You have this line:

Dim rng1, rng2, cell1, cell2 As Range

Which is declaring:

rng1 as variant
rng2 as variant
cell1 as variant
cell2 as range

You can put them all on one line but it should read like this:

Dim rng1 As Range, rng2 As Range, cell1 As Range, cell2 As Range

 

[Trevor Kaltenbrunn]

Hi Trevor,

Welcome to the MrExcel Forum.

What is wrong. Does the code not do what you want, or is it throwing an error. If it is an error, can you tell us what the error is and what line of code triggers it.

Additionally, as an FYI, when you declare your variables, you must declare what each one is separately. You have this line:

Dim rng1, rng2, cell1, cell2 As Range

Which is declaring:

rng1 as variant
rng2 as variant
cell1 as variant
cell2 as range

You can put them all on one line but it should read like this:

Dim rng1 As Range, rng2 As Range, cell1 As Range, cell2 As Range

I have tried you solution and still have the same problem. The code seems to work, but only the first line of inventory is updated. i have attached a link of the file in my dropbox so that you can see what i mean. I am very new at doing macros and this is frustrating me no end. https://www.dropbox.com/s/xzwwckuymbgosk4/Book1.xlsm?dl=0

 

[dmt32]

Hi,
try this update to your code & see if it does what you want

Sub updateInventorys()
'we declare 4 variables
    Dim InvoiceRng As Range, InventoryRng As Range
    Dim cell As Range
    Dim m As Variant
    
    With ThisWorkbook
        With .Worksheets("Sheet1")
    Set InvoiceRng = .Range(.Range("B16"), .Range("B" & .Rows.Count).End(xlUp))
        End With
        With .Worksheets("Sheet2")
    Set InventoryRng = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
        End With
    End With
    
    For Each cell In InvoiceRng
        m = Application.Match(cell.Value, InventoryRng, 0)
        If Not IsError(m) Then
            With InventoryRng.Cells(CLng(m), 2)
                .Value = .Value - cell.Offset(, 2).Value
            End With
        End If
    Next cell


End Sub

Do be mindful that if you run the code & then make an adjustment to the invoice - the code will deduct the same amounts again from the inventory.

Hope Helpful

Dave

 

[Trevor Kaltenbrunn]

Hi,
try this update to your code & see if it does what you want

Sub updateInventorys()
'we declare 4 variables
    Dim InvoiceRng As Range, InventoryRng As Range
    Dim cell As Range
    Dim m As Variant
    
    With ThisWorkbook
        With .Worksheets("Sheet1")
    Set InvoiceRng = .Range(.Range("B16"), .Range("B" & .Rows.Count).End(xlUp))
        End With
        With .Worksheets("Sheet2")
    Set InventoryRng = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
        End With
    End With
    
    For Each cell In InvoiceRng
        m = Application.Match(cell.Value, InventoryRng, 0)
        If Not IsError(m) Then
            With InventoryRng.Cells(CLng(m), 2)
                .Value = .Value - cell.Offset(, 2).Value
            End With
        End If
    Next cell


End Sub

Do be mindful that if you run the code & then make an adjustment to the invoice - the code will deduct the same amounts again from the inventory.

Hope Helpful

Dave

Hi Dave,

Thanks so much it seems to work.

One question if i save the invoice into PDF then delete the original (working) invoice will the values stay as is or will they change back. I have written a code that saves and updates the invoice number to next invoice number, that is working fine.

 

[dmt32]

Hi,
If you are referring to the Inventory sheet2 then value should only change each time you run the macro.

Point I was making is if you run the macro then find you have to update the invoice and run macro again, you will deduct the same amounts for each entry from inventory a second time.

If this is likely to be a problem you should be able to adapt your code to manage.

Dave

 

[Trevor Kaltenbrunn]

Thank you so much, it has really helped me.