Look up Next Entry

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
56
Hi, I have the following code which looks up the value in cell G6 in sheet "Fulfilment Tracker - Data" and pull the adjacent cell data into cell G8 of sheet "Fulfilment Tracker -Input"


Code:
Set Table_Range = Workbooks("Fulfilment Tracker - Data").Sheets("All Data").Range("A2:T20000")
Set LookupValue = Workbooks("Fulfilment Tracker - Input").Sheets("Update").Range("G6")


Result1 = Application.WorksheetFunction.VLookup(LookupValue, Table_Range, 2, False)

Workbooks("Fulfilment Tracker - Input").Sheets("Update").Range("G8").Value = Result1
This works fine if there is only one entry, but I want to assign a macro to pull the next entry matching cell G6, unless there is only a unique record.

Any help would be appreciated.
Thanks
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,981
If there are more than one matching entries, where do want to paste them? The first would go to G8 but what about the other matching entries?
 

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
56
Thanks for the reply mumps. If there is one entry, this will put it in G8. What I'm trying to do is create a button that would move to the next matching entry and replace the data in G8 (based on the lookup of the next entry).

Hope that makes sense.

Cheers
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,981
That would mean that G8 will always contain the last matched entry. Is this correct?
 

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
56
No, the 1st macro will always pull the 1st matching entry. What I'm looking for is another macro that will pull the data for the next matching entry.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,981
In order to do what you want, we would need a way of keeping track of the row number of the last found entry. This could be done by using a "helper" cell somewhere on either sheet. We would need a cell that will never be used. On which sheet and which cell would you prefer the helper cell? It looks like you are using two workbooks. Is this correct? As well, this can be done using only one macro.
 

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
56
If I add a count in column B in the Data workbook would that help. Yes I'm using 2 workbooks. Certainly more efficient if can be done by 1 macro. Its like clicking on the next record on access database but just for the matching entries.

Cheers
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,981
Make sure both workbooks are open and place this macro in a standard module in the "Fulfilment Tracker - Data" workbook. I used Range("AA1") in the "All Data" sheet as the helper cell. It should start out blank. When you run the macro the first time, it will find the first entry. Each time you run the macro after that, it will find the next entry.
Code:
Sub FindNextEntry()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, fnd As Range
    Set srcWS = Sheets("All Data")
    Set desWS = Workbooks("Fulfilment Tracker - Input").Sheets("Update")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If srcWS.Range("AA1") = "" Then
        Set fnd = srcWS.Range("A:A").Find(desWS.Range("G6"), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            srcWS.Range("AA1") = fnd.Row
            desWS.Range("G8") = fnd.Offset(0, 1)
        End If
    Else
        Set fnd = srcWS.Range("A" & srcWS.Range("AA1").Value + 1 & ":A" & LastRow).Find(desWS.Range("G6"), LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            srcWS.Range("AA1") = fnd.Row
            desWS.Range("G8") = fnd.Offset(0, 1)
        End If
    End If
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,145
Messages
5,466,935
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top