My two-parameter vlookup function (VBA) skips past the top row in my data range

Question

I need some help debugging this VBA function. I know I'm probably glossing over a simple error, but I can't figure it out.

I have a VBA function that acts as a two-parameter VLOOKUP. Here it is:

Function TwoParameterVlookup(Data_Range As Range, Col As Integer, Parameter1 As _
    Variant, Parameter2 As Variant) As Variant

'Declare Variables
Dim Cell
Dim Current_Row As Integer
Dim No_Of_Rows_in_Range As Integer
Dim No_of_Cols_in_Range As Integer
Dim Matching_Row As Integer

'set answer to N/A by default
TwoParameterVlookup = " "
Matching_Row = 0
Current_Row = 1

No_Of_Rows_in_Range = Data_Range.Rows.Count
No_of_Cols_in_Range = Data_Range.Columns.Count


'Check if Col is greater than number of columns in range
If (Col > No_of_Cols_in_Range) Then
    TwoParameterVlookup = CVErr(xlErrRef)
End If

If (Col <= No_of_Cols_in_Range) Then
    Do Until ((Current_Row > No_Of_Rows_in_Range) Or (Matching_Row <> 0))
        If ((Data_Range.Cells(Current_Row, 1).Value = Parameter1) And (Data_Range.Cells(Current_Row, 2).Value = Parameter2)) Then
            Matching_Row = Current_Row
        End If
        If Matching_Row <> 0 Then
            TwoParameterVlookup = Data_Range.Cells(Matching_Row, Col)
        End If
        Current_Row = Current_Row + 1
    Loop
End If

End Function

Originally, the function was closing the loop before the final row of the data range could be read. I fixed this, but the current problem still remains. I'll select the data range for my function to vlookup, but it will simply skip past the top row of my selected range.

I put your code into vba and it is working just like it is supposed too. I'm not sure what to make of it lol. Both the top and bottom of the data set are working. Have you had any luck so far? — Blake Turner, Aug 10 '18 at 21:09
No luck so far. Hmm, maybe the issue is coming from elsewhere. Much appreciated — christopherhlee, Aug 10 '18 at 21:18
Just and FYI: Here are ways to do this with current worksheet functions: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another — Scott Craner, Aug 10 '18 at 21:48

score 0 · Answer 1 · answered Aug 10 '18 at 22:41

A bit shorter/simpler:

Function TwoParameterVlookup(DataRange As Range, Col As Integer, _
              Parameter1 As Variant, Parameter2 As Variant) As Variant

    Dim rw As Range

    If Col > DataRange.Columns.Count Then
        TwoParameterVlookup = CVErr(xlErrRef)
    Else
        For Each rw In DataRange.Rows
            If rw.Cells(1) = Parameter1 And rw.Cells(2) = Parameter2 Then
                TwoParameterVlookup = rw.Cells(Col).Value
                Exit For
            End If
        Next rw
    End If

End Function

My two-parameter vlookup function (VBA) skips past the top row in my data range

1 Answers1