Trying to modify the Vlookupall UDF

[maxxev]

Hi, I found this usefull function online, however I would like it to check 2 columns instead of one; this is the original:

Function vlookupall(sSearch As String, rRange As Range, _
    Optional lLookupCol As Long = 2, Optional sDel As String = ",") As String
'Vlookupall searches in first column of rRange for sSearch and returns
'corresponding values of column lLookupCol if sSearch was found. All these
'lookup values are being concatenated, delimited by sDel and returned in
'one string. If lLookupCol is negative then rRange must not have more than
'one column.
'Reverse("moc.LiborPlus.[URL unfurl="true"]www")[/URL] PB 16-Sep-2010 V0.20
Dim i As Long, sTemp As String
If lLookupCol > rRange.Columns.Count Or sSearch = "" Or _
    (lLookupCol < 0 And rRange.Columns.Count > 1) Then
    vlookupall = CVErr(xlErrValue)
    Exit Function
End If
vlookupall = ""
For i = 1 To rRange.Rows.Count
    If rRange(i, 1).Text = sSearch Then
        If lLookupCol >= 0 Then
            vlookupall = vlookupall & sTemp & rRange(i, lLookupCol).Text
        Else
            vlookupall = vlookupall & sTemp & rRange(i).Offset(0, lLookupCol).Text
        End If
        sTemp = sDel
      End If
Next i
End Function

I would like to make the code check column A for a "Y" as well as checking for sSearch.

Can someone please explain to me how to do that. I tried using and AND statement for the value of (1,A) but that didnt work... (i'm not very good at VBA).

Cheers

 

[Bluejay07]

Hi Maxxev,

If you are looking for assistance with VBA, you should try the VBA forum. This forum is for VB5 and VB6.
forum707

I don't know the code you are using, although it looks like rRange(i,1) is reading a grid, which is usually represented by rRange(row, col) format. This function seems to be checking the first column and every row. (Note: many grids start with 0, not 1 - eg. If you have three columns say A, B, C, then Col. A = col 0, B = Col 1, etc.) If you use the 'AND' key word, then all conditions must be true for the rest to be evaluated. You may need to add another if statement, such as:

For i = 1 To rRange.Rows.Count
    If rRange(i, 1).Text = sSearch Then
        If lLookupCol >= 0 Then
            vlookupall = vlookupall & sTemp & rRange(i, lLookupCol).Text
        Else
            vlookupall = vlookupall & sTemp & rRange(i).Offset(0, lLookupCol).Text
        End If
        sTemp = sDel
      End If

   If rRange(i, 1).Text = "Y" Then
      ...
   End if
Next i

You indicated that you wanted to verify 2 columns, therefore you will need to change the '1' as both if statements will verify the same column.

I hope this helps.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[maxxev]

Hi, thanks for the comment, wrong forum probably explains slow answers then!!, sorry I didn't realise there was a difference lol.

I managed a work around but I would love to know how to do this properly so willpost in the correct forum.

Thank you for your thoughts, you are probably right about the additional If statement, but at the moment the value wouls still have been processed into the cell already before it gets to the 2nd if statement.

Cheers.

 

[SkipVought]

hi,

Change the first Optional variable to a STRING and enter the column(s) you want, separated by commas, for instance...

=vlookupMULTI(G1,$A$2:$E$9,[b]"2,5,3"[/v])

Function vlookupMULTI(sSearch As String, rRange As Range, _
    Optional [b]sLookupCol As String = "2"[/b], Optional sDel As String = ",") As String
'vlookupMULTI searches in first column of rRange for sSearch and returns
'corresponding values of column lLookupCol if sSearch was found. All these
'lookup values are being concatenated, delimited by sDel and returned in
'one string. If lLookupCol is negative then rRange must not have more than
'one column.
'Reverse("moc.LiborPlus.[URL unfurl="true"]www")[/URL] PB 16-Sep-2010 V0.20
    Dim i As Long, sTemp As String[b], LU_Cols, j As Integer
    
    LU_Cols = Split(sLookupCol, ",")
    
    For j = 0 To UBound(LU_Cols)
        If Not IsNumeric(LU_Cols(j)) Then Exit Function
        If LU_Cols(j) > rRange.Columns.Count Then Exit Function
        If LU_Cols(j) < 1 Then Exit Function
    Next[/b]
    
    For i = 1 To rRange.Rows.Count
        If rRange(i, 1).Text = sSearch Then[b]
            For j = 0 To UBound(LU_Cols)
                vlookupMULTI = vlookupMULTI & sTemp & rRange(i, CInt(LU_Cols(j))).Text
                sTemp = sDel
            Next[/b]
            
          End If
    Next i
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]