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-09T17:01:21+00:00

    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.

    Was this answer helpful?

    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-02-09T14:21:50+00:00

    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

    Was this answer helpful?

    0 comments