I wonder if there's a better way to do this?

I want to build an array of line numbers where a date falls within the
Dates in Cols K and M.

The Date in 'K' is always before 'M' and they're formatted as text "03
Jan 1965" etc.

At the moment I'm using a For Next Loop to look at every line
in the sheet. This seems wasteful as there are several thousand
lines and only 100 or less cases that will match. OR - does this not
matter i.e. every line would need checking anyway?

Here's what I have :-

Variable A holds the target date.
(LastRow is from Ron de Bruin's Last Function)
----
For f = 2 To LastRow
If inTheRange(.Cells(f, "K"), .Cells(f, "M"), A) Then
ReDim Preserve l(UBound(l) + 1)
l(UBound(l)) = f
End If
Next f

Function inTheRange(DateIn, DateOut, ThisDate) As Boolean
If IsDate(DateIn) = True Then
If IsDate(DateOut) = True Then
If CDate(ThisDate) = CDate(DateIn) And CDate(ThisDate) <=
CDate(DateOut) Then inTheRange = True
End If
End If
End Function
----

Thanks for any suggestions.

Cheers - Kirk

When I'm not sure how long something actually take I measure the time

StartTime = Date
For f = 2 To LastRow
If inTheRange(.Cells(f, "K"), .Cells(f, "M"), A) Then
ReDim Preserve l(UBound(l) + 1)
l(UBound(l)) = f
End If
Next f
EndTime = Date
msgbox("the routine took : " & (endtime - StartTime))


If the time is short, don't worry. You can use advance filters to get just
the numbers but you would still need to put the numbers into an array. There
are always tradeoff of keeping the code simple verses making the code run
quicker. right now you code is very simple. if the time is short then don't
worry.

"kirkm" wrote:


I wonder if there's a better way to do this?

I want to build an array of line numbers where a date falls within the
Dates in Cols K and M.

The Date in 'K' is always before 'M' and they're formatted as text "03
Jan 1965" etc.

At the moment I'm using a For Next Loop to look at every line
in the sheet. This seems wasteful as there are several thousand
lines and only 100 or less cases that will match. OR - does this not
matter i.e. every line would need checking anyway?

Here's what I have :-

Variable A holds the target date.
(LastRow is from Ron de Bruin's Last Function)
----
For f = 2 To LastRow
If inTheRange(.Cells(f, "K"), .Cells(f, "M"), A) Then
ReDim Preserve l(UBound(l) + 1)
l(UBound(l)) = f
End If
Next f

Function inTheRange(DateIn, DateOut, ThisDate) As Boolean
If IsDate(DateIn) = True Then
If IsDate(DateOut) = True Then
If CDate(ThisDate) = CDate(DateIn) And CDate(ThisDate) <=
CDate(DateOut) Then inTheRange = True
End If
End If
End Function
----

Thanks for any suggestions.

Cheers - Kirk