Using the find In VBA

Hi Tim

Apologies for the late response, I've been very busy with projects at work and at home.

Regarding your project

I share Andreas's opinion that

"...In order to establish a secure process here, the data would have to be restructured so that it is the same in all files..."

In addition

The key to finding a Product is to establish a reliable and unique SKU/CODE/Product ID thru all the Inventory files.

So dear Tim

This is a huge and ambitious project, that will demand a lot of effort and hours from both sides to solve it,

I suggest we should have a Video chat/meeting via Skype or Zoom, so we could share our thoughts with you, and get a better understanding of your scenario. I will be happy to have Andreas at the meeting.

This Tuesday February-21st, I'll be available from 1:00 pm London time.

Do let me know if that's OK for you, so I could send you the link for the meeting.

Regards

Jeovany

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.

Tim,

That is correct. IMHO the first step for you is to understand how my code works.

And especially what happens if things goes wrong.

An invoice means a clear structure for you and me, but for VBA in Excel there are just cells anywhere which can contain anything! There are soo many scenarios where a simple typo can mess up everything.

So after you made your positive tests with valid input, try to make the code fail!

A good code should catch all invalid input and throw a meaningful error message for the end user. The end user should never see "Enter debug mode?" nor that the VBA editor opens and the code stops anywhere. That's the worst case scenario from my point of view.

Have a nice weekend, if you have further questions, I'll be back on Monday.

Andreas.

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?

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

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