Why is my custom function referencing the wrong column values?

[ez08mba]

What I'm trying to do:
I decided to create my own custom function to iterate two columns, searching criteria in one column and returning values from the second, and then concatenating them into a single cell.


Problem:
Despite my code returns the correct column numbers passed into variables as integers for Cell(x,x) calls, the values getting returned are off by one or more columns. For example:

=VerticalSearch(B20, B:B, D:D, ",")
The code correctly interprets the parameters B:B and D:D as 2 and 4 respectively. The first is the lookup column, the second the return value. However, the macro is actually looking in column 3 (C:C) for the lookup column, yet the variable locals show correct column value of 2.

=VerticalSearch(B20, D:D, B:B, ",")
When out of curiosity I switched them around, D:D (4) was actually searching in column 10.


What have I done
Closed my excel instances, reopened the workbook, and I still get the issue.

The code in question:

Function VerticalSearch(LookupValue As String, LookupColumn As Range, SearchColumn As Range, Char As String)
    Dim rows1 As Integer
    Dim cols1 As Integer
    Dim cols2 As Integer
    cols1 = LookupColumn.Column
    cols2 = SearchColumn.Column
    rows1 = LookupColumn.Cells(1000, cols1).End(xlUp).Row
    
    Dim r As Long
    Dim msg As String
    For r = 1 To rows1
    
        MsgBox LookupColumn.Cells(r, cols1).Text
        If LookupColumn.Cells(r, cols1).Text = LookupValue Then
            msg = Concatenate(msg, SearchColumn.Cells(r, cols2).Text, Char)
        End If
        
    Next
    
    If Len(Trim(msg)) = 0 Then
         VerticalSearch= Empty
    Else
         VerticalSearch= msg
    End If
    
    
End Function


' ....................................................
Private Function Concatenate(m As String, v As String, c As String)
    If Len(m) > 0 Then
        m = c & " " & v
    Else
        Concatenate v
    End If
    
End Function

 

[shg]

If LookupColumn.Cells(r).Text = ...

 

[JoeMo]

Your cols1 and cols2 variables are causing the shift of columns. Once you specify a range they become relative references. So, for example:

rows1 = LookupColumn.Cells(1000, cols1).End(xlUp).Row
with cols1 = 2 (nominally col B)
gives you the last filled row for col c which is the second column relative to the range LookupColumn )(colB).

Try this:

Function VerticalSearch(LookupValue As String, LookupColumn As Range, SearchColumn As Range, Char As String)
    Dim rows1 As Integer
    rows1 = LookupColumn.Cells(Rows.Count, 1).End(xlUp).Row
    Dim r As Long
    Dim msg As String
    For r = 1 To rows1
    
        MsgBox LookupColumn.Cells(r, 1).Text
        If LookupColumn.Cells(r, 1).Text = LookupValue Then
            msg = Concatenate(msg, SearchColumn.Cells(r, 1).Text, Char)
        End If
        
    Next
    
    If Len(Trim(msg)) = 0 Then
         VerticalSearch = Empty
    Else
         VerticalSearch = msg
    End If
    
    
End Function

This may need some further adjustment, I have not tested it. I have not looked at you Concatenate function at all.

 

[ez08mba]

JoeMo & shg:

JoeMo, your suggestion worked excellently! Your explanation makes sense. I learned a further power of using a range as a reference. So I understand why my column value is always 1 in this case.

Sorry for the late response, but thanks very much!

 

[JoeMo]

JoeMo & shg:

JoeMo, your suggestion worked excellently! Your explanation makes sense. I learned a further power of using a range as a reference. So I understand why my column value is always 1 in this case.

Sorry for the late response, but thanks very much!

You are welcome - thanks for the reply.