Inventory control

[ndendrinos]

In this attachment the inventory is kept thanks to a UserForm ...

I cannot use this set up and need to adapt the code to
deduct from sheet "Stock" what is shipped out as per sheet "Invoice" reflecting the Invoice number, and the qty shipped. (I will delete sheet1 and the UserForm)
Thank you

[Bob Phillips]

I don't understand what this code should do.

[ndendrinos]

Hello xld
I'm trying to keep my inventory sheet "Stock" up to date by deducting materials shipped on Sheet Invoice.
The first line is the intial inventory received by me on 08/10/2009 under my PO# 145889 (10 of each fruit)

Sold under Inv #2
3 Bananas (NOT 3 Melons ... wrong in my attachment)
And 2 Oranges

The macro will input on first empty row of "Stock" the invoice# and qty (in negatives) shipped in the appropriate columns having matched the product sold on the invoice to the same header in "stock"

I know you have figured all that and that you question my mistake (Melons should have been Bananas)

Thank you

New and Corrected attachment enclosed

[ndendrinos]

Little progress but still in the dark.
In this attachment and from sheet "Invoice" the code identifies the items sold and transfers the qty sold to the respective columns in sheet "Stock"

what needs to be addressed is:

A) Qty of each respective item should appear on Sheet "Stock" under the proper column
(In the attachment I refer to "Set x = Range("A17")" and that's why I get that value in each identified column in sheet "Stock")

B) All data tranferred to Sheet "Stock" should go to the first empty row

C) Nedd to have Value "H2" shown in column B " Inv/PO #"

Thank you

[rbrhodes]

Hi nden,

Try this out.

What it does:

- Builds list of items from list in Sheet Invoice Range B51...
- Echos items value as header in sheet Stock (formulas)
- Gets item cost from same list (formulas)
- Gets Invoice number from Sheet Invoice Range D51
- Increments Invoice number on 'Clear'
- Puts todays date in H2
- Everything else...

[ndendrinos]

Hello again rbrhodes ... OR dr
It sure does what I need it to do .... Wife No1 and I have decided to name the first born after you ...
Cheers, Nick

[rbrhodes]

Son # 2 (of 2) is Nicolas - Good name!

rb/dr

[ndendrinos]

What can I say .. I've sweat buckets all day long trying to get my original file going AND just solved one of the three outstanding issues (convoluted as it may be but it works... ) so please allow me to persevere a little bit longer before I give up.


Still need to resolve:
A) Qty of each respective item should appear on Sheet "Stock" under the proper column
(In the attachment I refer to "Set x = Range("A17")" and that's why I get that value in each identified column in sheet "Stock")

B) All data tranferred to Sheet "Stock" should go to the first empty row in their respective columns.

[ndendrinos]

SOLVED (with simple work around) : All data tranferred to Sheet "Stock" should go to the first empty row in their respective columns.

One to go:
A) Qty of each respective item should appear on Sheet "Stock" under the proper column
(In the attachment I refer to "Set x = Range("A17")" and that's why I get that value in each identified column in sheet "Stock")

[mdmackillop]

Products is a Dynamic Range =OFFSET(Invoice!$B$17,0,0,COUNTA(Invoice!$B$17:$B$35),1). This range should have Data Validation set against the list of possible products to avoid inevitable errors eg "Kiwis"

Sub test()
Dim TgtRw As Long
Dim Dt
Dim Inv
Dim Prodlist As Range
Dim c As Long

With Sheets("Invoice")
Dt = .Range("H3").Value
Inv = .Range("H2").Value
End With
With Sheets("Stock")
TgtRw = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Prodlist = .Range("A3:L3")
End With
Cells(TgtRw, 1) = Dt
Cells(TgtRw, 2) = Inv
For Each Cel In Range("Products")
c = Application.Match(Cel, Prodlist, 0)
Cells(TgtRw, c) = -Cel.Offset(, -1)
Next
End Sub

[Aussiebear]

Hmmm.... so glad you didn't mention Aussies.

[ndendrinos]

Bonjour mdmackillop ,
In my attachment "test4" there cannot be now or in the future a typo with any of les fruits.
So can I do without the naming "product" ? AND if so how would I change the code?
Here is what I've done AND "Lesfruits=Nothing" Why?


Sub test()
Dim TgtRw As Long
Dim Dt
Dim Inv
Dim Lesfruits As Range
Dim c As Long

With Sheets("Invoice")
Dt = .Range("H3").Value
Inv = .Range("H2").Value
End With
With Sheets("Stock")
TgtRw = .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Lesfruits = .Range("A3:L3")
End With
Cells(TgtRw, 1) = Dt
Cells(TgtRw, 2) = Inv
For Each Cel In Range("Lesfruits")
c = Application.Match(Cel, Prod, 0)
Cells(TgtRw, c) = -Cel.Offset(, -1)
Next
End Sub


But most important is : how do I call the code from sheet "Invoice"