Rick -
I often bury these in a cell's custom number format, but I am reluctant to
do so in a TEXT function or Format (in VBA). I have to understand this when
I review it in six months, and hiding something in a format makes it harder
to see. You could have further obfuscated the statement by hiding "Dates: "
in the first format, and you'd be more confused upon later review.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
Since the letters "to" are not metacharacters to the TEXT function, and at
the risk of making your statement just a little bit more obfuscated, you can
shorten it a tad more...
ActiveCell.FormulaR1C1 = _
"=""Dates: ""&TEXT(MIN(R[3]C:R[65000]C),""dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"
Rick
"Jon Peltier" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> You can shorten it a lot:
>
> ActiveCell.FormulaR1C1 = _
> "=""Dates: ""&TEXT(MIN(R[3]C:R[65000]C),""dd mmm yyyy"")&" _
> & """ to ""&TEXT(MAX(R[3]C:R[65000]C),""dd mmm yyyy"")"
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Rob" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Using Excel 2000. The below lines are on two rows, I'd like to have as a
>> few more rows so it's easier to read albeit the _ (space underscore)
>> doesn't like breaking the lines. Is this possible?
>>
>> Also, I don't really like the [65000] in the code but I don't know any
>> other way to get the MIN and MAX dates from a range that charges
>> frequently. The below code places in a file then range values.
>>
>> ActiveCell.FormulaR1C1 = _
>> "=""Dates: ""&TEXT(MIN(R[3]C:R[65000]C),""dd"")&""
>> ""&TEXT(MIN(R[3]C:R[65000]C),""mmm"")&""
>> ""&TEXT(MIN(R[3]C:R[65000]C),""yyyy"")&"" to
>> ""&TEXT(MAX(R[3]C:R[65000]C),""dd"")&""
>> ""&TEXT(MAX(R[3]C:R[65000]C),""mmm"")&""
>> ""&TEXT(MAX(R[3]C:R[65000]C),""yyyy"")"
>>
>> Thanks, Rob
>>
>
>