VBA for 2-D Lookup with Multiple Returned Values

[Essay76]

Hey there! Have a table with headers of specific locations and a column of dates going back several years. I need a person to be able to search by location and value and return the corresponding date(s) in a pop up box (msgbox). I could probably write a formula but I'm guessing there is a possible better way to do it in VBA using a command button. Would also need some error handling if a value is not found for a specific location. Any help would be much appreciated

For example - The user would enter "Location 1" in one cell (A1) and "55" in another cell (B1). A box would pop up with 1/2/2010 and 1/3/2010. Assume the table is on the same worksheet as the reference cells and command button
[TABLE="width: 500, align: center"]
<TBODY>[TR]
[TD]Date
[/TD]
[TD]Location 1
[/TD]
[TD]Location2
[/TD]
[TD]Location 3
[/TD]
[/TR]
[TR]
[TD]1/1/2010
[/TD]
[TD]50
[/TD]
[TD]44
[/TD]
[TD]42
[/TD]
[/TR]
[TR]
[TD]1/2/2010
[/TD]
[TD]55
[/TD]
[TD]52
[/TD]
[TD]47
[/TD]
[/TR]
[TR]
[TD]1/3/2010
[/TD]
[TD]55
[/TD]
[TD]51
[/TD]
[TD]47
[/TD]
[/TR]
[TR]
[TD]1/4/2010
[/TD]
[TD]51
[/TD]
[TD]47
[/TD]
[TD]44
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[NeonRedSharpie]

Function lookupStuff(location As String, value As String, rng As Range) As String   
    Dim arr() As Variant
    arr = rng
    
    Dim maxcol As Long
    Dim maxrow As Long


    
    With rng
        maxrow = .Rows.Count
        maxcol = .Columns.Count
    End With
    
    For x = 1 To maxcol
    
        Select Case arr(1, x)
        Case Is = location
            For y = 1 To maxrow
                Select Case arr(y, x)
                Case Is = value
                    lookupStuff = lookupStuff & arr(y, 1) & vbNewLine
                Case Else
                End Select
            Next y
        Case Else
        End Select
    Next x
    
End Function




Sub testers()


    MsgBox lookupStuff(Range("A1"), Range("B1"), Range("E1:H5"))
    


End Sub

I used a "testers" sub to specify the range and inputs. Just call the "lookupstuff" from within your button push.

 

[Essay76]

Thank you very much! One more question. If no dates are returned, instead of getting a blank box, how would you return a message of "No Dates Found"?

I can't do it through error handling as this does not cause an error in the function.

 

[NeonRedSharpie]

Function lookupStuff(location As String, value As String, rng As Range) As String   
    Dim arr() As Variant
    arr = rng
    
    Dim maxcol As Long
    Dim maxrow As Long


    lookupStuff = "No Dates Found"
    With rng
        maxrow = .Rows.Count
        maxcol = .Columns.Count
    End With
    
    For x = 1 To maxcol


        Select Case arr(1, x)
        Case Is = location
            For y = 1 To maxrow
                Select Case arr(y, x)
                Case Is = value
                    If lookupStuff = "No Dates Found" Then lookupStuff = ""
                    lookupStuff = lookupStuff & arr(y, 1) & vbNewLine
                Case Else
                End Select
            Next y
        Case Else
        End Select
    Next x
    
End Function

You have the return value default to "no dates found". Then, if it doesn't find anything, that's the value.

 

[Essay76]

Works like a charm. Thanks again. This function will come in very handy im sure with other worksheet I use.

 

[NeonRedSharpie]

You can also use it within your workbook. It might not return the dates on different lines though...didn't think about that.