Lookup "Next" function in VBA

[Rashie]

Hey All,

I'm trying to make something to help my team.
I'm using a vlookup function and it's working great like it always does BUT some values I use to lookup have multiple results (i.e. 2 or 3.) 90% of my values are unique.

I'm looking for a way to either have a VBA button that says "Next" and displays the next result the lookup can find, or have multiple results from the same data (i.e. Possible option 1, possible option 2, possible option 3.)

[TABLE="width: 392"]
<tbody>[TR]
[TD][/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8821.90 - This is my value I'm using to search[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name:[/TD]
[TD]XXX (=VLOOKUP(B2,USDREP!A:T,2,FALSE)[/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]XXX (=VLOOKUP(B2,USDREP!A:T,3,FALSE)[/TD]
[/TR]
[TR]
[TD]Access[/TD]
[TD]Online-only(=VLOOKUP(B2,USDREP!A:T,8,FALSE)[/TD]
[/TR]
[TR]
[TD]Price Code[/TD]
[TD]INSTIT (=VLOOKUP(B2,USDREP!A:T,13,FALSE))[/TD]
[/TR]
[TR]
[TD]Item Number[/TD]
[TD]XXX (=VLOOKUP(B2,USDREP!A:T,14,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]


Can anybody suggest a way to have multiple lookups? To confirm, Value 8821.90 may have 2 seperate unrelated results, XXX & YYY.

Thanks for your help,

Rashie.

 

[DanteAmor]

It works in the following way:
- Create a button on your sheet.
- Assign the macro to the button.
- Put the price in cell B2.
- Press the button.


- Press the button to go to the next record.

Note: The macro requires an auxiliary cell to take control of the next record, cell D1, you can change it for the one you want.

Sub lookup_Next()
    Dim sh1 As Worksheet, sh2 As Worksheet, f As Range, i As Long, aux As Range
    Set sh1 = ActiveSheet
    Set sh2 = Sheets("USDREP")
    Set aux = sh1.Range("[COLOR=#ff0000]D1[/COLOR]")   'Auxiliary cell
    If aux.Value = "" Then i = 1 Else i = aux.Value
    Set f = sh2.Range("A:A").Find(sh1.Range("B2").Value, after:=sh2.Range("A" & i), LookIn:=xlValues, lookat:=xlWhole)
    sh1.Range("B4").Value = sh2.Cells(f.Row, 2).Value
    sh1.Range("B5").Value = sh2.Cells(f.Row, 3).Value
    sh1.Range("B6").Value = sh2.Cells(f.Row, 8).Value
    sh1.Range("B7").Value = sh2.Cells(f.Row, 13).Value
    sh1.Range("B8").Value = sh2.Cells(f.Row, 14).Value
    aux.Value = f.Row
End Sub