Got it with:
Range("B7").Select
Selection.FormulaArray = _
"=MAX(--RIGHT(RC[3],4), --RIGHT(RC[5],4), --RIGHT(RC[7],4), --RIGHT(RC[9],4))"

Thanks
"Corey" wrote in message
...
JMB,
I sem to be abl;e to use the :
=MAX(--RIGHT(D7,4), --RIGHT(G7,4), --RIGHT(I7,4), --RIGHT(K7,4))

with some success.

Is it possible to have the cell ("B7") entered as that formula complete
with Shift+ALT+Enter ?
As i am placing the other St values there froma userform.
I change the values to be (0) zero if no value is needed, to get the
formula to work.

But need to allow this formula to be input into B7 when the userform is
unloaded.

Corey....

"JMB" wrote in message
...
Could not think of a way to do it w/o converting each cell. Assuming
none of
the cells are empty or blank, one way:

=MAX(--RIGHT(D7,4), --RIGHT(G7,4), --RIGHT(I7,4), --RIGHT(K7,4))

Or if there is no data where the last 4 characters are numeric in the
cells
between D7 and K7:
=MAX(IF(ISNUMBER(--RIGHT(D7:K7,4)), --RIGHT(D7:K7,4)))
array entered


Is your project dependent on the cells containing the text "st " (since
all
of the data is in this format it seems to me the "st " is more for
presentation and wouldn't be absolutely necessary for your worksheet
formulae
to work). Is it an option to use a custom number format of
"st "#

That way, your data is presented as "st 1234", but the cell will actually
contain the number 1234. Then you can dispense w/ having to pull out the
number part and just use the MAX function normally. You could use
Find/Replace to replace
st<space
replace with = <nothing-leave it empty

to get rid of the text "st " in your data and convert it to numbers. You
would have to be careful the Find/Replace doesn't affect other data on
the
worksheet you do not intend to convert (perhaps copy the data you want
converted to a sheet by itself-do the Find/Replace and then copy it back
to
it's original location).

If you do try it, be sure to backup.



"Corey" wrote:

Thanks for the reply JMB.
Is it any easier to get the MAX of these cells:
B7=MAX(D7,G7,I7,K7) witht he same "st xxxx" format ?


"JMB" wrote in message
...
Array formulae cannot use entire columns (for versions prior to
XL2007).
Also, I get errors if any of the cells in the target range are empty
or
contain data where the last 4 characters are non-numeric. Also, the
formula
cannot go into cell B1 since that cell is part of the formula's
argument
(creating a circular reference).

Be aware that any data that ends w/4 numbers will be included in this
formula, not just data that begins w/ "st ".

This seemed to work okay on my machine, change range as needed.
=MAX(IF(ISNUMBER(--RIGHT(A1:I100,4)),--RIGHT(A1:I100,4)))


"Corey" wrote:

I get a #NUM! Value for some reason with all options here ?




"RaceEend" (rot13) wrote in message
...
Corey wrote:

I need to find the Max Value in a Worksheet,
But the Value will always be in a "st 1234" format.
That is:
"st"+ Space then 4 digits.
I want to find the MAX Numerical value in the entire sheet, and
place this value in B1.
How can I do this ?

Something like :
B1= MAX(sheet!A:I(Right(4)) ???

Corey....

Corey,

B1 = MAX(--RIGHT(Sheet!A:I,4))
Entered as a matrix formula (= CTRL + SHIFT + ENTER)


--