You could use an array formula in a worksheet to give you the answer

=Max(if(A1:A2000="B",B1:B2000))

so in VB

vVal = Evaluate("Max(if(A1:A2000=""B"",B1:B2000))")

That work for me
vVal will return zero if no B's are in column A. Note that you can not
examine an entire column with an array formula and to speed execution you
should limit the range you examine anyway.

--
Regards,
Tom Ogilvy


"Rune_Daub" wrote in message
...
thx.. I got it to work, but now I got a little harder problem.

I can easily find the letter "B" in the Columns(1)

Now I have a value in Columns(2) that I need to act on.

So that in column 2 the value can be either 1, 2, 3 or 4.

I need to find the highst value in column 2, for all columns that has
the letter B in column A, and end it with a msgbox that says
either

There is no letter B
There is a letter B and the highst value is (either 1,2,3 or 4)

How do I combine the previous script with a relative function that
reads the HIGHST value.

I know this might be a little tricky, but I hope someone got the guts
to try it on.

thx
Rune Daub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/