(In Excel 2003) When my user selects an item from a drop-down on a
UserForm, I need to search through the range of cells on the worksheet
to find the last previous entry of that item. The item will probably
be entered multiple times, so I need the one closest to the bottom of
the list.

Would it be faster to use a MATCH or LOOKUP function? Or do a Find?
Iteration is probably the slowest, especially when this thing hits a
few thousand entries, yes?

Ed

Hi
Find is probably the fastest. To find the last entry matching "grp" in
column A

Set lastCell = Range("A:A").Find("grp",
SearchDirection:=xlPrevious)

This will search backwards from A1 and hence find the last cell with
"grp" in first.
regards
Paul

On Feb 26, 1:47*pm, Ed from AZ wrote:
(In Excel 2003) *When my user selects an item from a drop-down on a
UserForm, I need to search through the range of cells on the worksheet
to find the last previous entry of that item. *The item will probably
be entered multiple times, so I need the one closest to the bottom of
the list.

Would it be faster to use a MATCH or LOOKUP function? *Or do a Find?
Iteration is probably the slowest, especially when this thing hits a
few thousand entries, yes?

Ed

Thanks, Paul. I appreciate the boost.

Ed

On Feb 26, 7:30*am, wrote:
Hi
Find is probably the fastest. To find the last entry matching "grp" in
column A

* *Set lastCell = Range("A:A").Find("grp",
SearchDirection:=xlPrevious)

This will search backwards from A1 and hence find the last cell with
"grp" in first.
regards
Paul

On Feb 26, 1:47*pm, Ed from AZ wrote:



(In Excel 2003) *When my user selects an item from a drop-down on a
UserForm, I need to search through the range of cells on the worksheet
to find the last previous entry of that item. *The item will probably
be entered multiple times, so I need the one closest to the bottom of
the list.

Would it be faster to use a MATCH or LOOKUP function? *Or do a Find?
Iteration is probably the slowest, especially when this thing hits a
few thousand entries, yes?

Ed- Hide quoted text -

- Show quoted text -