Look up Next Entry

[Steven101]

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"

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

 

[mumps]

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]

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]

That would mean that G8 will always contain the last matched entry. Is this correct?

 

[Steven101]

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]

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]

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]

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.

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

 

[Steven101]

Thanks mumps, tweaked a wee bit and is perfect.

 

[mumps]

You are very welcome.