dealing with blank cells in a range
 

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?

dealing with blank cells in a range
 

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?

dealing with blank cells in a range
 

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

dealing with blank cells in a range
 

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?

dealing with blank cells in a range
 

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?