Please improve this simple implementation

[curiousgeorge408]

Please help me improve the implementation below. The VBA comment
highlights the improvement I am looking for. Thanks.

PS: Does "for each cell in rangeVar" always select from the first to
last cell in the range? I coulda sworn that I observed that it did
not, despite what the help page seems to say. But after I made some
changes to the spreadsheet, it consistently worked as I expected
(first to last). I wonder if the "for each" order depends on the
Excel evaluation order, which not only depends on the dependency
graph, but also seems to depend on the order in which cells have been
modified (I think).


' return relative index of last matching cell in range

Function matchlast(v As Variant, r As Range) As Long
Dim i As Long

' we should select from last to first cells in range;
' that would avoid searching the entire range every
' time. but I do not remember how to do that.

matchlast = 0
i = 0
For Each cell In r
i = i + 1
If cell = v Then matchlast = i
Next cell
End Function

 

[Chip Pearson]

Try something like the following. It returns the 1-based offset into
the range if the value is found or an #N/A error if not found.


Function MatchLast(V As Variant, R As Range) As Variant
Dim N As Long
With R.Cells
For N = .Count To 1 Step -1
If .Cells(N).Value = V Then
MatchLast = N
Exit Function
End If
Next N
End With
MatchLast = CVErr(xlErrNA) ' not found
End Function

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[curiousgeorge408]

        For N = .Count To 1 Step -1
            If .Cells(N).Value = V Then

Thanks, Chip. That's exactly what I needed.