Modifying a ConcatenateIF to ConcatenateIFS VBA UDF

[GabrielG]

Modifying a ConcatenateIF to ConcatenateIFS VBA UDF
Hi experts,
A few years back, AlphaFrog provided the following, very excellent VBA code (see below), to Concatenate with a conditional and remove duplicates from the string – effectively a ConcatenateIF. This UDF works for data of the form (in 2 columns of Company, Country):
Company1 CountryA
Company1 CountryB
Company1 CountryB
Company2 CountryC

Where identifying Company1 as the Condition, returns “CountryA, CountryB”. The code:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value > Condition Then
If InStr(1, strResult, Separator & ConcatenateRange.Cells(i).Value, 1) = 0 Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End Function

I’m now trying to modify the code to a ConcatenateIFS to allow for a second, date condition with data of the form (in 3 columns of Date, Company, Country):
Jan 1, 2017 Company1 CountryA
Jun 30, 2018 Company1 CountryB
Jan 1, 2019 Company1 CountryC
June 30, 2018 Company2 CountryC
Where identifying Company1 as the Condition, and say, April 30, 2018 as the Condition2 returns “CountryB, CountryC”. Notice, the first row is excluded because its date is less than Condition2, while the fourth row is excluded because it is Company2. I’ve modified the above VBA code to the below, which returns a “Compile error: Invalid Next control variable reference”

Function ConcatenateIfs(CriteriaRange As Range, Condition As Variant, Criteria2Range As Range, Condition2 As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim j As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count And Criteria2Range.Count <> ConcatenateRange.Count Then
ConcatenateIfs = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
For j = 1 To Criteria2Range.Count
If CriteriaRange.Cells(i).Value = Condition And Criteria2Range.Cells(j).Value > Condition2 Then
If InStr(1, strResult, Separator & ConcatenateRange.Cells(i).Value, 1) = 0 Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i, j
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIfs = strResult
Exit Function
ErrHandler:
ConcatenateIfs = CVErr(xlErrValue)
End Function

Interestingly, if in the original code I modify “If CriteriaRange.Cells(i).Value > Condition Then” and apply it to the date column only, it performs the proper date exclusion. So that instruction seems correct.

The problem appears to be the way I’ve combined both Condition and Condition2 through the code. Unfortunately, my workplace has Excel 2013, so I couldn’t use TextJoin (if that happened to work in this case, anyhow).

Your help is greatly appreciated!

Gabriel

[GabrielG]

Update:
I've found one part of the error.
My For statements begin with i then j.
If I reverse the Next from i then j, to j then i, the UDF clears the "Compile error: Invalid Next control variable reference".
However, the date condition no longer works, so picks up all CountryA rows into the strResult, rather than excluding any rows where the entry date comes before the Condition2 date.
I would have thought the:
"If CriteriaRange.Cells(i).Value = Condition And Criteria2Range.Cells(j).Value > Condition2 Then" would create the exclusion.
What am I missing?

[GabrielG]

Update 2:
The ConcatenateRange.Cells(i) must be changed to ConcatenateRange.Cells(i, j). This seems to set up the exclusion propertly.
However, now if the date being evaluated as Condition2 is greater than all the dates for the Condition Company1, I'm getting the concatenated Company2 country list, so it seems the If And still isn't being evaluated properly.

Update 3: The above Update 2 is not working.

[ExcelTime]

This works but is capturing all the elements not the subset. Solution was to change the ConcatenateRange.Cells(i) to ConcatenateRange.Cells(j). Note: Also changed Condition2 to =

Please Login or Register  to view this content.