Range.column

[Rune_Daub]

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)

 

[Tom Ogilvy]

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

 

[Rune_Daub]

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

 

[Tom Ogilvy]

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.