Share via

Using the find In VBA

Anonymous
2023-02-09T14:01:31+00:00

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 thoughts were to use the find function, activate the cell, and then subtract the quantity from the active cell. I am just starting with the macro, but I am running into an object error with my find.

Sub Deduct()

Dim C As String

'Deduct the Quantity from Stock

Workbooks.Open "C:\Users\furnd\Desktop\Test Deduct.xlsm"

With Workbooks("Test Deduct.xlsm").Sheets("Sheet1").Range("A1:A2000")

Set C = .Find(What:="A1").Value

End With

Workbooks("Test Deduct.xlsm").Activate

Sheets("Sheet1").Select

C.Offset(0, 1).Select

End Sub

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

47 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-22T20:40:04+00:00

    Andreas

    I am still going over your code. Sorry it is taking me a while. Jeovany and I met over video chat and he gave me so options that can be done with power query that I am looking into as well. I do have a couple of questions based on what I am seeing in your code. I tried to split the questions up based on the sections of the code.

    Where are the items?

                  It is ok if there are no items in the invoice because I can have invoices with payments only.

    'Be sure we have a numerical quantity for each used item

    'Get the other file

               Can’t I set this to auto open the file?

    'Find the headings

    Do I need this since I changed the stock file to a single file?  Can I just search for the item in the stock file?  I will probably need to write a check to make sure the item is in the stock file but do I need to worry about the headings?

    'Be sure we can find all the items and there is a valid stock value

               If my invoice is using data validation for the item field and pulling it out of stock file do I need to check to make sure the item is there? Does “Set Stock = Intersect(Stock.EntireColumn, Dest.EntireRow)” set the row based on finding the matching sku and if so why can’t I do this with the find and offset that I was using previously.  In the statement (If MsgBox("Qty " & Qty & " > Stock " & Stock & "! Continue?", vbOKCancel + vbDefaultButton2 + vbQuestion, Title & ": " & Item) = vbCancel Then Exit Sub) does this open a message box with the option to continue?

    FindHeader:

               Completely confused as to what this is doing.

    Errorhandler:

               Will the code enter this anytime there is an error that is not accounted for in the above code?

    Private Function GetWorkBook(ByVal WorkBookName As String) As Workbook

    Not quite sure what this is doing. Is it checking to see that a workbook that we are going to be using is open?

    0 comments
  2. Anonymous
    2023-02-21T13:34:05+00:00

    Here is the link to the meeting https://meet.google.com/kyt-zkbh-kon

    See you at 5pm(12pm)

    Regards

    0 comments
  3. Anonymous
    2023-02-21T13:16:58+00:00

    That should be fine.

    0 comments
  4. Anonymous
    2023-02-21T12:32:40+00:00

    Hi Tim

    Good morning

    I was expecting finishing my morning appointment earlier (before1pm local time). Unfortunately we have some delays here.

    Let's meet up later in the day.

    What about 5pm London time I.e 12 pm your local time.

    See you soon

    Regards

    0 comments
  5. Anonymous
    2023-02-20T19:08:51+00:00

    Here is the link to the current files.

    https://we.tl/t-5mgWQCjlG1

    I will be happy to talk with you tomorrow. I believe you are 5 hours ahead of me would you possibly be able to meet at 1:30 London time?

    That's great

    Thanks Tim

    I'll post the link tomorrow with time enough before our meeting

    Regards

    0 comments