Using the find In VBA

Even if I can only automate some of the process it will save us a large amount of time in the long run.

Here is the link to the wetransfer documents https://we.tl/t-7d1ZrUWicC

Sorry this was so long. I wanted to make sure you understood exactly what my overall goal was.

Everything is fine, without your detailed explanation nobody outside of your company would understand it.

There are a few problems to be clarified and unfortunately I have to say in advance that IMHO it will not work in this way with the existing data.

Okay, let's start with an Invoice, I tried to enter "B070-31" in the Invoice file, but your file wont let me. A short look, problem solved, here's the data I entered:

Problem a)

How should the Invoice file know that "B070-31 Dresser" can be found in "Ashley Bedroom.xlsm" and not in "Ashley Stationary.xlsm"?

I can guess that every manufacturer has it own file, but how they are named?

Maybe there's an "Kitchen Ashley.xlsm" file anywhere? In which file is the item "B123-99 Knife"?

That can not be automated by a code.

Problem b) If we take a look into "Ashley Bedroom.xlsm" we can find

That would mean the code has to split "B070-31 Dresser" by "-" to get the Series and by " " to get the SKU and skip the remaining part "Dresser". Possible but what do we get for the next item "Q H/O Rails"?

There is no "-" in there, okay we can use any symbol as delimiter and that leads to "Q" as Series and "H" as SKU... i bet that can't be found in file "Albion Whatever.xlsm". Am I right?

Problem c)

Let us take a look into "Washington Brothers.xlsm" and compare the headings in row 5 with "Ashley Bedroom.xlsm"

They are no the same, especially there is no "Stock" in there, I can guess that "Warehouse" should mean the same...

Automation means that a process is always the same, but this only works if the data structure is always the same. And you don't have that in any way, sorry.

It all looks similar enough for a human and if I would know your job I can work with your files, no question. But VBA is like a blind man, it can't see anything.

In order to establish a secure process here, the data would have to be restructured in such a way that it is the same in all files. That means days or weeks of work. And then you would still have to write and test all the code.

All this is my opinion, let's see what Jeovany has to say about it.

Andreas.

Thank you for the information.

I have continued to work on this and started to see some of the issues that you were talking about. The inventory files were the first thing I created to start moving our company away from pen and paper and onto computers. They were done in a rush and I agree they need to be cleaned up so they all look the same. I have already made some adjustments to my procedure. I plan on housing the entire inventory in 1 file and using the separate files to pull from that file so my employees can still open files separately. I have included the link to the updated invoice file and the test for pricing and stock. The Test Ledger is still the same. I will have to add in the rest of the dealers before I finish. I'm also wondering if I can create an input box so when merchandise comes in we can open that box and input the sku and the quantity that came in. I would really like to keep everyone out of the master inventory file if possible.

https://we.tl/t-pX9DYGDyHL

Hi Tim,

I like to give you an example how a code can look like to process an invoice, based on your latest files.
Add the code below to the code module of sheet "Invoice".

The most of the code is error handling, which is IMHO essential for this kind of tasks.

I ignored the part to open the stock file, because "open a file" sounds simple, but a good VBA code has to care:
a) If the file exists at all
b) Can be opened
c) Is not write protected
d) Can be saved
All that would lead to more code which IMHO is just confusing in the meaning of this thread.

I would change the Invoice.xlsm to a template, so there is no need to clear the data from the invoice after the process.
Furthermore in that way we can make sure that the Invoice is saved before we reduce the stock (each with a different name/date/customer) and so is available later if needed, e.g. print out a copy.
Also a copy as PDF as Jeovany noted is a good idea.
An automated backup of the stock file is mandatory.

Andreas.

Sub Deduct()
Const Title = "Deduct"
Dim Header As Range, HeaderName As String
Dim Source As Range
Dim Item As Range, LastItem As Range, Qty As Range, Total As Range, Items As Range
Const WbStockName As String = "Test Pricing and Stock for invoice.xlsm"
Dim Wb As Workbook
Dim SKU As Range, Stock As Range, Dest As Range

On Error GoTo Errorhandler

'Find the headings / limits
Set Source = Me.Cells
HeaderName = "Item"
GoSub FindHeader
Set Item = Header
HeaderName = "Qty"
GoSub FindHeader
Set Qty = Header
HeaderName = "Total"
GoSub FindHeader
Set Total = Header

'Where are the items?
Set LastItem = Intersect(Total.Offset(-1).EntireRow, Item.EntireColumn)
If LastItem = "" Then Set LastItem = LastItem.End(xlUp)
If LastItem.Row = Item.Row Then
MsgBox "No items in invoice", vbOKOnly + vbInformation, Title
Exit Sub
End If
Set Items = Range(Item.Offset(1), LastItem)

'Be sure we have a numerical quantity for each used item
For Each Item In Items
If Not IsEmpty(Item) Then
Set Qty = Intersect(Qty.EntireColumn, Item.EntireRow)
If Not IsNumeric(Qty) Or IsEmpty(Qty) Then
Qty.Select
MsgBox "Invalid Qty in row " & Qty.Row, vbExclamation, Title & ": " & Item
Exit Sub
End If
End If
Next

'Get the other file
Set Wb = GetWorkBook(WbStockName)
If Wb Is Nothing Then
MsgBox "File not open, please try again.", vbInformation, Title & ": " & WbStockName
Exit Sub
End If

'Find the headings
Set Source = Wb.Sheets(1).Rows(1)
HeaderName = "Sku/Description"
GoSub FindHeader
Set SKU = Header
HeaderName = "Stock"
GoSub FindHeader
Set Stock = Header

'Be sure we can find all the items and there is a valid stock value
For Each Item In Items
If Not IsEmpty(Item) Then
Set Qty = Intersect(Qty.EntireColumn, Item.EntireRow)
Set Dest = SKU.EntireColumn.Find(Item)
If Dest Is Nothing Then
MsgBox "Item " & Item & " not found", vbCritical, SKU.Address(0, 0, External:=True)
Exit Sub
End If
Set Stock = Intersect(Stock.EntireColumn, Dest.EntireRow)
If Not IsNumeric(Stock) Then
MsgBox "Invalid Stock in row " & Stock.Row, vbExclamation, Title & ": " & Item
Exit Sub
End If
If Qty > Stock Then
If MsgBox("Qty " & Qty & " > Stock " & Stock & "! Continue?", vbOKCancel + vbDefaultButton2 + vbQuestion, Title & ": " & Item) = vbCancel Then Exit Sub
End If
End If
Next

'Reduce the stock
For Each Item In Items
If Not IsEmpty(Item) Then
Set Qty = Intersect(Qty.EntireColumn, Item.EntireRow)
Set Dest = SKU.EntireColumn.Find(Item)
Set Stock = Intersect(Stock.EntireColumn, Dest.EntireRow)
Stock = Stock - Qty
End If
Next

MsgBox "Done. Please save the stock file", vbInformation, Title
Exit Sub

FindHeader:
Set Header = Source.Find(HeaderName, LookIn:=xlValues, LookAt:=xlWhole)
If Header Is Nothing Then
MsgBox "Header '" & HeaderName & "' not found!", vbCritical, Source.Address(0, 0, External:=True)
Exit Sub
End If
Return

Errorhandler:
If Err.Source = "" Then Err.Source = Application.Name
Debug.Print "Source : " & Err.Source
Debug.Print "Error : " & Err.Number
Debug.Print "Description: " & Err.Description
If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _
Err.Description & vbNewLine & vbNewLine & _
"Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then
Stop 'Press F8 twice
Resume
End If
End Sub

Private Function GetWorkBook(ByVal WorkBookName As String) As Workbook
'Return the workbook that name is like WorkBookName, Nothing if not open
Dim fso As Object 'FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'Path given?
If Len(fso.GetParentFolderName(WorkBookName)) > 0 Then
'Compare the full path of each open workbook
For Each GetWorkBook In Workbooks
If StrComp(GetWorkBook.FullName, WorkBookName, vbTextCompare) = 0 Then
Exit Function
End If
Next
ElseIf InStrRev(WorkBookName, ".") > 0 Then
'We must exact match if an extension is given
On Error GoTo ExitPoint
Set GetWorkBook = Workbooks(WorkBookName)
Else
'Without an extension it can be a new file too
On Error GoTo SearchIt
Set GetWorkBook = Workbooks(WorkBookName)
Exit Function
SearchIt:
On Error GoTo ExitPoint
If (InStr(WorkBookName, "?") > 0) Or (InStr(WorkBookName, "*") > 0) Then
For Each GetWorkBook In Workbooks
If fso.GetBaseName(GetWorkBook.Name) Like WorkBookName Then
Exit Function
End If
Next
Else
For Each GetWorkBook In Workbooks
If StrComp(fso.GetBaseName(GetWorkBook.Name), WorkBookName, vbTextCompare) = 0 Then
Exit Function
End If
Next
End If
End If
ExitPoint:
End Function

Andreas,

Thank you, I am going to take my time and read through your code to make sure I can understand it. I do have a follow questions already though.

In regards to, "I ignored the part to open the stock file, because "open a file" sounds simple, but a good VBA code has to care:"

I think you are just saying that you ignored it in regards to the current code that you wrote and I would need to add it in myself. If you didn't mean it that way and meant it as its not needed. Then my question is this. If I am going to be using the invoice file from multiple computers, since I have a couple people in my office, won't I have to have code to open the Test Pricing and Stock for invoice.xlsm so the code can deduct out of it automatically? Otherwise, wouldn't the person creating the invoice have to manually open the file so the code can check it?