Lookup the last value in a range and selecting the date

[Sjodom]

Hello,

I have a worksheet that has ranges of string values that contain a date within the first 5 characters. I am able to locate the date from the cell:

=MID(R10,1,5)

But I am now having difficulty locating the last cell in a general range with value. I have found macros to assist:

Selection.End(xlToRight).Select

But is then incompatible with the above cell formula.

Any ideas?

 

[Aladin Akyurek]

...

Aladin -
This makes more sense. After playing with the formula and changing the values, I was able to see what sections effected the result. Specifically, the +0 at the end and the REPT("z",255). I am not sure why you chose "z" or 255, other values seemed to produce the same outcome.

...

REPT("z",255) has the same role as 9.99999999999999E+307 that you see in the link I provided. It's lexically/alpahbetically the latest value in most natural languages. These are values which are deemed not to occur in data we can be expected to work with. The former is intended to be used with references housing text, the latter with references housing numeric data. The latter is a limit value in Excel and as such is reported in Excel's Help. The big text, which the former boils down to, is designed by Mark W. in analogy with the latter. And, as such there is no need to create variants of these values.