Prev: button on macro recorder doesn't toggle
Next: Copy Cell from one sheet to specific Col value
From: Peter on 12 Apr 2010 21:34
Hello

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?

Thanks


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.

For instance:

Dim wks as worksheet
dim FoundCell as range
dim myStr as string

myStr = "1234"

set wks = worksheets("Somesheetnamehere")

With wks
With .Range("A:A")

Set Foundcell = .Cells.Find(what:=myStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)

end with
end with

if foundcell is nothing then
'...
else
msgbox foundcell.offset(0,2).value
end if

(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.




Peter wrote:
>
> Hello
>
> 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?
>
> Thanks

--

Dave Peterson
 | 
Pages: 1
Prev: button on macro recorder doesn't toggle
Next: Copy Cell from one sheet to specific Col value