From: Peter on 12 Apr 2010 21:34
I've got a spreadsheet that I need to search on a part number and get it's
price, however there may be multiple part numbers, and the determining
factor would be the revision of that part. such as ...
Part number Rev Price
1234 A 13.00
1787 G 16.50
1234 B 15.00
using the .find method, is there a way to do a search for a part number, and
if multiples are found, get the one that has the latest rev?
From: Dave Peterson on 13 Apr 2010 08:31
If you're partnumbers/revision data is sorted nicely, you could use .find and
search from the bottom and use the first one found.
Dim wks as worksheet
dim FoundCell as range
dim myStr as string
myStr = "1234"
set wks = worksheets("Somesheetnamehere")
Set Foundcell = .Cells.Find(what:=myStr, _
if foundcell is nothing then
(untested, uncompiled. Watch for typos.)
Starting in the first cell (after:=.cells(1)) and looking up
(searchdirection:=xlprevious) is what makes this work.
If your data isn't sorted -- and you can't sort it, I think you'll have to loop
through your data (.find and .findnext???) and keep track of the latest rev.
ps. Just a tip.
If you're doing an Edit|Find manually, you can shift click on the Find Next
button and it searches in reverse order.
> I've got a spreadsheet that I need to search on a part number and get it's
> price, however there may be multiple part numbers, and the determining
> factor would be the revision of that part. such as ...
> Part number Rev Price
> 1234 A 13.00
> 1787 G 16.50
> 1234 B 15.00
> using the .find method, is there a way to do a search for a part number, and
> if multiples are found, get the one that has the latest rev?