Check in VBA if the next iterator is the end of the loop-CodePudding

I have written the following function for this problem I have encoutered. I wanted to create a Vlookup in excel that can concatenate multiple values from a search into a single cell.

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim row As Range
Dim result As String
result = ""
For Each r In lookuprange
    If r = lookupval Then
        result = result & r.Offset(0, indexcol - 1) & ", "
    End If
Next r
MYVLOOKUP = result
End Function

The result of this is the picture below

example solution

If you notice in the solution that I have created, the for each loop above works to concatenate all the values together but because of the structure of the loop it adds an extra comma onto the end. I know how I could check if the next iteration will be null in python, but I am struggling in VBA. Essentially I need to check if the iteration that the loop is on is the last item it can add and then exclude the comma somehow. Is there anyway to somehow remove the last comma at the end of the loop or create a check if the current iteration is the last item?

CodePudding user response：

This is a stand pattern when concatenating a string with a delimiter:

 result = Left(result, Len(result) - Len(", "))

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Const Delimiter As String = ", "
    Dim row As Range
    Dim result As String
    result = ""
    For Each r In lookuprange
        If r = lookupval Then
            result = result & r.Offset(0, indexcol - 1) & Delimiter
        End If
    Next r
    
    If Len(result) > 1 Then MYVLOOKUP = Left(result, Len(result) - Len(Delimiter))
End Function

CodePudding user response：

or do this:

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    Dim row As Range, result As String, sep as string
    For Each r In lookuprange
        If r = lookupval Then
            result = result & sep & r.Offset(0, indexcol - 1)
            sep = "," 'populate delimiter after first value
        End If
    Next r
    MYVLOOKUP = result
End Function