Using VLookup function to find a cell to change its value

Question

I am trying to connect to workbooks to track orders. One workbook(Book1) displays the order#, total qty and current qty(completed so far) alongside a list of the weeks orders in the production office. The other workbook(Book2) is at the workstation for the operator to enter the new order number and current quantity as parts are completed.

The first half of the code works fine. It successfully updates the order# and pastes it to Book1 from Book2. What I am having trouble accomplishing is updating the cell in the "status" column of the table with the corresponding order# that was just pasted into the workbook to either a 1 or 2. I have the table formatted to where a blank cell is red(order not active), 1 = yellow(order is open) and 2 = green(order complete).

I tried the code below using an "IF" off of the Order Count being 0 because it will reset before the pasting of the new Order#. NOTE: orders may not be completed in the order they are listed so it has to be some type of lookup. I can't just find the last empty cell in the "status" column.

Update* FIGURED IT OUT! Code below now works in case anyone elses comes across this thread!

Thank you to everyone in the comments below.

Private Sub CommandButton1_Click()
   Dim wbEntry As Workbook
   Set wbEntry = ThisWorkbook
   Dim wbCount As Workbook
   Set wbCount = Workbooks("MO# Count.xlsm")

   wbEntry.Sheets("Sheet1").Range("B3").Copy

   wbCount.Activate
   wbCount.Worksheets("Golf Cart").Range("V5").Select
   ActiveCell.PasteSpecial xlPasteValues

  Dim Fnd As Range
   Set Fnd = Sheets("Golf Cart").Range("A:A").Find(Sheets("Golf Cart").Range("V5").Value, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Set Fnd = Fnd.Offset(0, 2)
   End If

   Fnd.Value = 1


   ActiveWorkbook.Save

   wbEntry.Activate

   Application.CutCopyMode = False

   wbEntry.ActiveSheet.Range("H2").Select

End Sub

Actually there is no question. If you get errors please tell which and in which line. If you don't get errors please tell what exactly is wrong with the code. Please note that [*“It’s not working”* is not helpful](http://idownvotedbecau.se/itsnotworking/). • Also this line `Status = Application.WorksheetFunction.VLookup(MONum, MOlist, 3, False).Select` must be without `.Select`: You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jan 28 '20 at 15:01
to find the last cell in a column research `range.End(xlUp)` — SmileyFtW, Jan 28 '20 at 15:03
I need to be able to edit the cell that the Vlookup finds. If not select then how do i edit the value of that cell after finding it? — mdavis0510, Jan 28 '20 at 15:07
@SmileyFtW I stated that I cannot simply find the last empty cell in that column because I can't guarantee the orders will be ran in the order on the list. — mdavis0510, Jan 28 '20 at 15:08
Try using [FIND](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) to return a reference to the found cell. — Darren Bartrup-Cook, Jan 28 '20 at 15:10
@DarrenBartrup-Cook How would I go about writing that in VBA? Never used it in there before. — mdavis0510, Jan 28 '20 at 15:16
@mdavis0510 Sorry, I didn't mean to offend you. If you tried the find method (no matter if it didn't work, that's actually why you obviously asked here), then just [edit] your question and show the code you have tried with `find`. If we can see the code you tried we can help you to fix it, or tell what you did wrong with `find`. But we can only help you to fix it if we see what you did wrong. — Pᴇʜ, Jan 28 '20 at 15:48
@Pᴇʜ From what I read from the link Darren shared I do not think Find will work for what I am wanting to do. I need get the Vlookup I wrote above to work by directing me to that cell location then changing that cells value, not just recalling the value in the cell location. That is what I do not know how to do, since you stated .select following that is an invalid command. — mdavis0510, Jan 28 '20 at 16:01
Above I combined it with an IF because I have tried everything else that I know how to do and can't get it to work. I tried combing the vlookup with a paste command too and could not get it to work. — mdavis0510, Jan 28 '20 at 16:03
@mdavis0510 but that is exacly what Darren meant to say. If you read the [WorksheetFunction.VLookup method](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.vlookup) documentation you see that it returns a **value** not a **cell reference** (*"Searches for a value in the first column of a table array **and returns a value** …"*). But the find method does return a *cell reference*. That is why you need to use `find` if you need to access the cell and not just retrieve a value. — Pᴇʜ, Jan 28 '20 at 16:06
@Pᴇʜ The cell reference I am looking for has no value to search for. Also, even if i redid the conditional formatting to work with say, a zero, then to start off there would be an entire column of zeros. Please see the pictures I attached which might make it easier to understand what I am doing. — mdavis0510, Jan 28 '20 at 16:14
Is it possible to combine a .find method with a offset(2, 0).select? or something of that nature? Beacuse the cell I need to select will be in column "C" and the .find would take me to column "A". — mdavis0510, Jan 28 '20 at 16:16
Yes, you need `find` to find the cell that contains `YMM…759`. So actually then you have the **reference** (it does not work with a value) to that cell and put that reference to a variable `FoundCellReference` then you can use [Range.Offset property](https://learn.microsoft.com/en-us/office/vba/api/excel.range.offset) to move from that cell 2 columns to the right and write a value. Eg `FoundCellReference.Offset(ColumnOffset:=2).Value = 5`. Go for it, that's the right way. — Pᴇʜ, Jan 28 '20 at 16:18
I am getting a type miss-match error. `Dim FoundRef As Range Dim MO As Range Set MO = Range("V5") Set FoundRef = Cells.Find(What:="MO", After:=Range("A1:C31"), LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) FoundRef.Offset(ColumnOffset:=2).Value = 1` — mdavis0510, Jan 28 '20 at 17:15
@mdavis0510 sorry, but can you please [edit] it into your original question? Line breaks matter a lot in VBA and we cannot see them in the comments. Also please include in which line you get the error. • And why are you looking for `MO`? I thought you were trying to find the `YMM…759` number? Please clarify. — Pᴇʜ, Jan 28 '20 at 17:32
@Pᴇʜ I updated the post. I have MO set to a range value. The YM789 number will not be the same everytime. I need it to pull the search criteria from cell 'V5'. — mdavis0510, Jan 28 '20 at 17:39
FIGURED IT OUT! I wasn't putting a .Value after designating V5 as the search criteria. — mdavis0510, Jan 28 '20 at 18:28
@mdavis0510 but see my answer below there are some explanations and recommendations that you should not miss. — Pᴇʜ, Jan 29 '20 at 06:48

score 0 · Answer 1 · answered Jan 28 '20 at 18:27

If you write What:="MO" then you are searching literally for the text "MO" and not for the value in the variable MO. To use the variable you must write What:=MO
For every Range object specify in which workbook/worksheet it is or Excel might assume another worksheet than you thought.
Only search in Column A wsGolf.Columns("A").Find(…), if you search in Cells you search in all cells and of course you will always find what you are looking for in Range("V5") but that's not the result you want.
Check if Find was successfull: If FoundRef Is Nothing Then. You can only Offset from the found cell if one was actually found.
Never use .Select, .Activate or ActiveSheet always specify worksheets by their name for all Range and Cells objects etc. See How to avoid using Select in Excel VBA.

So something like below should help you:

Option Explicit

Private Sub CommandButton1_Click()
    Dim wbEntry As Workbook
    Set wbEntry = ThisWorkbook

    Dim wbCount As Workbook
    Set wbCount = Workbooks("MO# Count.xlsm")

    wbEntry.Worksheets("Sheet1").Range("B3").Copy

    Dim wsGolf As Worksheet
    Set wsGolf = wbCount.Worksheets("Golf Cart")

    wsGolf.Range("V5").PasteSpecial xlPasteValues

    Dim MO As Range
    Set MO = wsGolf.Range("V5")

    Dim FoundRef As Range
    Set FoundRef = wsGolf.Columns("A").Find(What:=MO, After:=wsGolf.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If FoundRef Is Nothing Then
        MsgBox "'" & wsGolf.Range("V5") & "' was not found.", vbCritical
        Exit Sub
    End If

    FoundRef.Offset(ColumnOffset:=2).Value = 1
End Sub

Using VLookup function to find a cell to change its value

1 Answers1