CODEWITHSUNDEEP
arraysexcelvbadateconcatenation
toms
toms

Reputation: 515

Compare date range to array values

I'm trying to create a function to concatenate a range of cells, IF their respective dates are Bigger or Equal ( >= ) to a specific date stored in a cell on the sheet.

In the code below, DatesRange_V is an array with a list of dates, and ThisDate is A1 containing a date. I wish to Loop through ConcatRange_V and concatenate them with a separator when the dates are bigger than ThisDay.

Function ConcatRangeIf() As String
Dim ConcatRange As Range
Dim DatesRange As Range

Set ConcatRange = Range("RP_Names")  'A list of names to concat
Set DatesRange = Range("RP_Dates")  'A list of dates

Dim Result As String
Dim Seperator As String
Dim FirstCell As Boolean

Dim ConcatRange_V() As Variant
Dim DatesRange_V() As Variant
Dim ThisDate As Variant

FirstCell = True
Seperator = ", "

ConcatRange_V = ConcatRange.Value
DatesRange_V = DatesRange.Value
ThisDate = Range("a1").Value ' A date on the current sheet

Dim i As Integer
For i = 1 To UBound(ConcatRange_V)
    If ConcatRange_V(i, 1) <> "" And DatesRange_V(i, 0) >= ThisDate Then
        If FirstCell Then
            Result = ConcatRange_V(i, 1)
        Else
            Result = Result & Seperator & ConcatRange_V(i, 1)
        End If
    End If
FirstCell = False
Next

ConcatRangeIf = Result
End Function

There seems to be a problem, as the cell I have placed =ConcatRangeif() is returning #VALUE!.

Upvotes: 0

Views: 183

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

if this is trully a copy of your code then the line:

ConcatRange_V = ConcatR.Value

ConcatR points to a variable that has not been assigned a value. It should read:

ConcatRange_V = ConcatRange.Value

Upvotes: 1

Related Questions