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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2023-02-28T23:53:28+00:00

    Hi Tim

    I've been very busy at work plus some home projects

    But I finally got the files done and working as intended.

    We could meet on Thursday at 7 pm London time via Video chat

    which link I'll post later.

    Thank you for being so patient

    Kind regards

    Jeovany

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-02-23T19:44:55+00:00

    Thank you for meeting with me on Tuesday. I have discussed some of your ideas with my boss and he is very intrigued by your idea to have incoming and out going fields with an initial stock and a current stock. I feel like maybe I didn't pay as much attention to that as I should have. Now I am having some issues with how I would go about programming that. Would it be possible for me to see those files and see if I can work my way through them or possibly meet over video chat to talk about them?

    Hi Tim

    Yes, sure no problem.

    I'll create the files, and show you how it works.

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-02-23T19:24:11+00:00

    Thank you for meeting with me on Tuesday. I have discussed some of your ideas with my boss and he is very intrigued by your idea to have incoming and out going fields with an initial stock and a current stock. I feel like maybe I didn't pay as much attention to that as I should have. Now I am having some issues with how I would go about programming that. Would it be possible for me to see those files and see if I can work my way through them or possibly meet over video chat to talk about them?

    Was this answer helpful?

    0 comments
  4. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-02-23T11:18:37+00:00

    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.

    Well, I don't know what you discussed with Jeovany, but for the purposes of this thread IMHO there is no way to bring about a solution with Power Query.

    Why?

    a) With Power Query you cannot write data to another file, you can only read data.
    That means you cannot transport any data from "Invoive.xlsm" to "Stock.xlsm" using PQ, which was your original concern!?

    b) If you implement PQ in the "Stock.xlsm" it is theoretically possible to import the data from the "Invoive.xlsm" and subtract it from the existing stocks.
    However, this is not trivial and requires special preparations.
    In addition, no error handling is possible in the process, i.e. an individual error message depending on the situation is impossible.
    Furthermore, you can only have ONE invoice file AND it must be the same for ALL users. The background is that the full path must be given in the query.
    The fundamental problem, however, is that the query in "Stock.xlsm" can only be executed if the file is opened by a user. And then the file is locked for ALL other users.

    I also favor solutions with Power Query, but IMHO it is not appropriate here and just a more complicated way to achieve the goal.
    Since Jeovany suggested it to you, please ask him for an example. Then you can test if it works for you.

    To my code:

    Sit back and let's reverse the direction of view:

    I have 2 Excel files open on my computer, in the current one I have numbers in an "ID" column and a number in the "Values" column.
    In the other file I also have an "ID" column and an "Stock" column.
    Now write me a code that subtracts the number under "Values" from "Stock" for the matching numbers under "ID".

    You think this is impossible?

    You can write code that does the job, no matter what the files look like or where the columns really are.
    This is how the code works I gave to you.

    Please don't try to change the code.
    In order to make adjustments here, you need some experience of what an object is and how it works in Excel.
    In addition, it is not necessary... except for some special circumstances where my code throws an error and you don't want to see it.

    The decisive point is to test whether all invoice data is always correctly transferred to the stock.
    Only then can you implement additional code such as how "Stock.xlsm" is opened safely and reliably and also display individual messages in the event of possible errors.

    Open the "Stock.xlsm" should not be done inside the existing code, but outside. That means you write another routine that checks / opens "Stock.xlsm", then calls my code and pass the workbook or worksheet variable as argument and then saves and closes "Stock.xlsm".
    The invoice can only be archived if this has worked without errors. That's the safe way.

    Andreas.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2023-02-23T06:44:12+00:00

    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

    do you mean multi users share invoice files or data?

    in one office or through internet?

    re:Otherwise, wouldn't the person creating the invoice have to manually open the file so the code can check it?

    if too manay datas and people in different places, time to consider web plus database to manage whole things.

    Was this answer helpful?

    0 comments