Using the find In VBA

Sub Deduct()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Here As Range, Where As Range, C As Range

Set Here = ActiveCell

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("A1", LookIn:=xlValues, LookAt:=xlWhole)
If C Is Nothing Then
MsgBox "Not found"
GoTo ExitPoint
End If

Here = Here - C.Offset(, 1)

ExitPoint:
Wb.Close
End Sub

Thank you,

This is along the right path of what I am looking for, but not exactly. I guess I should have been a little more exact on what I was trying to do. I have created an invoice file to pull pricing off of my sku list and I am not trying to set it up do deduct the items from my inventory when I complete the invoice. Below are screen shots of my invoice and inventory pages.

The important fields for the invoice page are the A column and the C column. I will need to search the inventory file (which I am probably going to have to adjust) for the match to Cell C6 in column B of the the Ashley Bedroom File (I am going to have to combine columns B and C probably) then I need to subtract Cell "A6" in the "Invoice" File from column F of the row that was found above. Then if the next row on the invoice has something in it I need to repeat until the next row is empty. I am not sure if this is even possible, but I have made some progress on it. Right now I am having alot of trouble finding the matching fields. I am using the test sheets screenshotted at the bottom and are very simple and still can't make if work.

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

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

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.