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-20T17:03:24+00:00

    Here is the link to the current files.

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

    I made a few adjustments to the invoice already in an attempt to automate the invoice #. I am still looking at Andreas code. However I can already see that you are both much more advanced then myself. I suspect I am not going to fully understand what he has written.

    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?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-02-20T14:58:48+00:00

    Thank you Tim, for accepting the video call/ meeting.

    Yet, I would strongly suggest focusing on the Tables structure, the Data cleansing, and getting a more reliable SKU/CODE/Product IDs for all the products in the store, because after our meeting you might have to change or create the macros again.

    Please., it would be a great advantage if you share with us, before our meeting., the latest version of the files involved in this project. Our answers will depend on that too.

    Do let us know when you ready.

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-02-20T13:56:40+00:00

    Jeovany and Andreas,

    Thank you very much for all the help. I am definitely aware that the scope of my project has vastly increased the more I get into it. I appreciate you being willing to do a video chat with me to discuss this. I would however like to ask if we can wait on that call for a little bit. I have written a code that works when I make sure the information is correct. However I do understand what Andreas is saying about trying to "break" the process with bad information. I would like to look at the code he wrote and make sure I understand it before moving forward. I have also already moved forward with combining all of the inventory into 1 file, but need to work on adjusting the pages my employees use to check on the inventory to read the centralized inventory page. Again thank both for all the help. I am going to work on makin alot of the changes that the two of you have suggested and cleaning thing up at this time. I hope you would still both be open to me asking questions in this thread as I come across them.

    Thank you

    Tim

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-02-20T07:06:12+00:00

    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

    Was this answer helpful?

    0 comments
  5. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-02-17T14:45:11+00:00

    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.

    Was this answer helpful?

    0 comments