i look for the max number in a range of codes in the following format:
E06001
E06002...

here is a section of my code:

Set rng = destWB.Worksheets("Sheet1").Range("D5:D" & Lr)

rmax = Application.Evaluate("MAX(VALUE(RIGHT(" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

****how can i tell it to ignore blank cells when looking for the max?

rmax = Application.Evaluate("MAX(VALUE(RIGHT(""00000""&" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

Would probably be the easiest.

--
Regards,
Tom Ogilvy


"steve" wrote in message
...
i look for the max number in a range of codes in the following format:
E06001
E06002...

here is a section of my code:

Set rng = destWB.Worksheets("Sheet1").Range("D5:D" & Lr)

rmax = Application.Evaluate("MAX(VALUE(RIGHT(" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

****how can i tell it to ignore blank cells when looking for the max?

that works....but i'm confused...what exactly does that do??

leith....
if there is a blank cell, i get an error (type mismatch)

"Tom Ogilvy" wrote:

rmax = Application.Evaluate("MAX(VALUE(RIGHT(""00000""&" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

Would probably be the easiest.

--
Regards,
Tom Ogilvy


"steve" wrote in message
...
i look for the max number in a range of codes in the following format:
E06001
E06002...

here is a section of my code:

Set rng = destWB.Worksheets("Sheet1").Range("D5:D" & Lr)

rmax = Application.Evaluate("MAX(VALUE(RIGHT(" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

****how can i tell it to ignore blank cells when looking for the max?

During the evaluation, It prepends 5 zeros to the left side the value of
each cell to guarantee each cell is at least 5 characters - this would
result in a zero value for blank cells and would not alter any values.

--
Regards,
Tom Ogilvy


"steve" wrote in message
...
that works....but i'm confused...what exactly does that do??

leith....
if there is a blank cell, i get an error (type mismatch)

"Tom Ogilvy" wrote:

rmax = Application.Evaluate("MAX(VALUE(RIGHT(""00000""&" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

Would probably be the easiest.

--
Regards,
Tom Ogilvy


"steve" wrote in message
...
i look for the max number in a range of codes in the following format:
E06001
E06002...

here is a section of my code:

Set rng = destWB.Worksheets("Sheet1").Range("D5:D" & Lr)

rmax = Application.Evaluate("MAX(VALUE(RIGHT(" & _
rng.Address(1, 1, xlA1, True) & _
",5)))")

****how can i tell it to ignore blank cells when looking for the max?

Hello Steve,

What impact do the blank cells have on determining the MAX value in the
Range?

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=504612