Range.column
 

Hi there.
Say I have a worksheet with 200 cells with mostly the letter "A" in
them... All in 1 column...
There might be 1 letter "B".
I need to find out if there is a letter "B" and then return a msgbox
that says "there is at least 1 letter "B" in this column. How do I do
that?

Hope you can help me quick..

Thx
Rune Daub (VBA n00b)



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

Range.column
 

Dim rng as Range
set rng = Columns(1).Find("B")
if not rng is nothing then
msgbox "There is at least 1 letter B"
else
msgbox "There is no letter B"
End Sub

--
Regards,
Tom Ogilvy

"Rune_Daub" wrote in message
...
Hi there.
Say I have a worksheet with 200 cells with mostly the letter "A" in
them... All in 1 column...
There might be 1 letter "B".
I need to find out if there is a letter "B" and then return a msgbox
that says "there is at least 1 letter "B" in this column. How do I do
that?

Hope you can help me quick..

Thx
Rune Daub (VBA n00b)



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

Range.column
 

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/

Range.column
 

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/