Using the find In VBA

Andreas,

Yes I am definitely a beginner when it comes to macro's, and I am certain that there are things that could be done to clean up my macros. I am trying to move my place of business away from doing everything with pen and paper to more automated processes. I am at the point where anything automated will be a huge time saver. I am also fully aware that there are programs out there that can do this, but those come with a huge cost in most cases. To be honest my first goal was to have an inventory that is on a shared folder so we can pull it from any of the 5 computers in the office instead of having to get up and walk to a different room to check the hand written inventory file. This is completed and working well. The only problem is that I have 40 files because if one person is in one file adjusting it then I need other people to be able to go in and make adjustments to other files. I split it up by manufacturer and department because of that. It has crossed my mind that there may be a way to pull create a file pulls information from a single inventory file, but I haven't gotten that far.

I guess what I am saying in way to many words is yes I probably need some help with everything I am doing. I can attach some of the files I have created to show the work I am doing, but again I am drawing a blank on how to do that.

Jeovany,

1.) I have already done this.

2.) Questioning what I need 2 sheets for. I have created what I call a Ledger sheet to track debits and credits per the sold to field on the invoice.

3.) I have created this already.

4.) saw that this is possible, but it was going to be one of my later steps.

5.) this is currently what I am working on, but it seems like you may be suggesting that I can do more then I thought was possible.

6.) Again, was hoping this was possible, but hadn't got there yet.

I would be happy to attach my current files, but I am drawing a blank on how to do that.

The next step is going to be to see if there is away to repeat steps after moving to the next line of the invoice. Do I need to do an offset for the SKU and Quantity Values and then do an If statement to see if the field is empty?

Tim,

No, that is not the next step. Jeovany has point out some notes how the whole project can be accomplished... but we you are fare away from that point.

If you are using OFFSET and SELECT, SELECTION, ACTIVECELL means for me you are a beginner. And from my point of view (with decades of experience) you may get a result if you go further in that direction, but at the end the code works not stable / safe for this kind of tasks.

I can teach you how to write code so that it works 100% safe and that it still works tomorrow if you change the layout of the sheet.

But that means work, for you and for me too. If you want to follow me, I'll work with you. If you just want a quick result that somehow works, I'm not interested.

Should we start with the basics? If so, upload a copy of your Invoice and Stock file on an Online File Hoster of your choice and post a download copy here.

Andreas.

Hi Tim

Regarding,

"I am trying to write a macro that will deduct an item from my stock (which is housed in a different workbook) when I hit the complete button."

My humble suggestions, (i.e. What I would do in your situation...)

1. House/Have in just ONE workbook the "Invoice" template and the "Stock database"
2. Add/Create 2 sheets to record the "Sales" and "Purchases".
3. With the help of the VLOOKUP(), or INDEX()/MATCH() functions, plus other Excel features like the Data Validation Dropdowns List, we could populate the Invoice as per our needs and fetch the relevant data from the Stock sheet

Once the Invoice is complete,

4. Create a macro to save the "Invoice" as a PDF file and store them under the customer names and indexed
5. Create a macro to update (add/deduct) the "Sales", "Purchases", and "Stock" sheets
6. Create a macro to clear the invoice

I hope this helps you

For further help, please, provide us with a copy of the Invoice and Ashley's Bedroom files, so we could work with them.

You may follow the instructions in this video to share them.

Regards

Jeovany

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

I made some adjustments and think I am moving along the right path. I need the Wb.Close to save the file before it closes which I think can be done with something along the lines of Wb.Close SaveChanges:=True. The next step is going to be to see if there is away to repeat steps after moving to the next line of the invoice. Do I need to do an offset for the SKU and Quantity Values and then do an If statement to see if the field is empty?

Sub Deduct()

Dim Wb As Workbook

Dim Ws As Worksheet

Dim Here As Range, Where As Range, C As Range

Dim SKU As String

SKU = Range("A1").Value

Quantity = Range("B1").Value

Debug.Print (SKU)

Set Wb = Workbooks.Open("C:\Users\furnd\Desktop\Test Deduct.xlsm")

Set Ws = Wb.Worksheets("Sheet1")

Set Where = Ws.Range("A:A")

Set C = Where.Find(SKU, LookIn:=xlValues, LookAt:=xlWhole)

If C Is Nothing Then 

MsgBox "Not found" 

GoTo ExitPoint 

End If

C.Offset(, 1).Select

Set Here = ActiveCell

Here = Here - Quantity

ExitPoint:

Wb.Close

End Sub