A better way to get Min and Max date

R

Rob

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
 
J

Jon Peltier

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
 
R

Rick Rothstein \(MVP - VB\)

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

Rob

Thanks Jon, just the job. I'll look at the other suggestion of a dynamic
range, see if I can figure it out!

Regards, Rob

Jon Peltier said:
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 said:
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
Click to expand...
Click to expand...
 
D

David

What is obfuscated? I am just a country boy.

Rick Rothstein (MVP - VB) said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

Unclear, confusing, obscure... basically, as used with coding, hard to read
or decipher.

Rick


David said:
What is obfuscated? I am just a country boy.

Rick Rothstein (MVP - VB) said:
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 said:
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
_______


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
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

You mean, SOP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Rick Rothstein (MVP - VB) said:
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


David said:
What is obfuscated? I am just a country boy.

Rick Rothstein (MVP - VB) said:
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


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
_______


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
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

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
_______


message 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 said:
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 said:
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
Click to expand...
Click to expand...
 
P

Peter T

You could have further obfuscated the statement by hiding "Dates: "
in the first format
Click to expand...

The D, e & s in "Dates" belong to Rick's metacharacters so it would indeed
obfuscate. <g>

Not quite sure what the 'e' does though.

Regards,
Peter T

Jon Peltier said:
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
_______


message 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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
K

kounoike

I'm not sure if this would match your case, but try this one.
assuming your data is continuously filled in Range.

Sub mytest()
Dim startrng As Range, endrng As Range
Dim rngstr As String

Set startrng = ActiveCell.Offset(3, 0)
Set endrng = startrng.End(xlDown)
rngstr = Range(startrng, endrng).Address(False, False, xlR1C1, , ActiveCell)
ActiveCell.FormulaR1C1 = "=""Dates: "" & " & _
"Text(Min(" & rngstr & "), ""dd mmm yyyy"")" & _
" & "" to ""& " & _
"Text(Max(" & rngstr & "),""dd mmm yyyy"")"
End Sub

keiji
 
J

Jon Peltier

Well, you'd have to bury them within more quotes. The "e" could be reserved
for scientific notation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Peter T said:
You could have further obfuscated the statement by hiding "Dates: "
in the first format
Click to expand...

The D, e & s in "Dates" belong to Rick's metacharacters so it would indeed
obfuscate. <g>

Not quite sure what the 'e' does though.

Regards,
Peter T

Jon Peltier said:
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
_______


in
message 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 said:
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
_______


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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

You could have further obfuscated the statement by
hiding "Dates: " in the first format....
Click to expand...

I didn't want to confuse the reader by doing that. <g>

But, as long as you insist <bg>, it could be done either this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""\Dat\e\s: dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

or this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""""""Dates: """"dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

Yes, either one of these is much better. <vbg>

Rick
 
P

Peter T

The "e" could be reserved for scientific notation.
- Jon
Click to expand...

Yes that it, but needs it a little more to work; from Help

"Scientific notation To display numbers in scientific format, use "E-,"
"E+," "e-," or "e+" exponent codes in a section. If a format contains a 0
(zero) or # (number sign) to the right of an exponent code, Excel displays
the number in scientific format and inserts an "E" or "e". The number of 0's
or #'s to the right of a code determines the number of digits in the
exponent. E- or e- places a minus sign by negative exponents. E+ or e+
places a minus sign by negative exponents and a plus sign by positive
exponents."

=TEXT(1000000,"#e-#")
1e6

Regards,
Peter T
 
K

kounoike

Hi Rick

Your first formula doesn't work in my environment (Excel 2003, Xp SP2).
Escape sequence seems not to work for me.
Am i doing somthing wrong?
and i wonder how can i apply this method to format like below.
Dates is surrounded by quotes

"Dates:"2008 Feb 08 to 2008 Mar 02

i can't find the way to make this.

keiji

Rick Rothstein (MVP - VB) said:
You could have further obfuscated the statement by
hiding "Dates: " in the first format....
Click to expand...

I didn't want to confuse the reader by doing that. <g>

But, as long as you insist <bg>, it could be done either this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""\Dat\e\s: dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

or this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""""""Dates: """"dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

Yes, either one of these is much better. <vbg>

Rick
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

I'm using XL2003 also, but on Vista... both posted formulas work fine for me
(I had tested them before posting and I just re-tested them to make sure).
I'm not sure what difference XP over Vista could possibly make. Anyone else
out there want to try them out? Just put some dates in A3 downward (not
important how many), then make A1 the active cell and run the line of code
(you can so in the Immediate window if you want).

Rick


kounoike said:
Hi Rick

Your first formula doesn't work in my environment (Excel 2003, Xp SP2).
Escape sequence seems not to work for me.
Am i doing somthing wrong?
and i wonder how can i apply this method to format like below.
Dates is surrounded by quotes

"Dates:"2008 Feb 08 to 2008 Mar 02

i can't find the way to make this.

keiji

Rick Rothstein (MVP - VB) said:
You could have further obfuscated the statement by
hiding "Dates: " in the first format....
Click to expand...

I didn't want to confuse the reader by doing that. <g>

But, as long as you insist <bg>, it could be done either this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""\Dat\e\s: dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

or this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""""""Dates: """"dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

Yes, either one of these is much better. <vbg>

Rick
Click to expand...
Click to expand...
 
K

kounoike

for more details, This is what i got.

Using your first formula:
\1at\20\0: 01 Feb 2008 to 03 May 2008

Using your second formula is:
Dates: 01 Feb 2008 to 03 May 2008

keiji

Rick Rothstein (MVP - VB) said:
I'm using XL2003 also, but on Vista... both posted formulas work fine for
me (I had tested them before posting and I just re-tested them to make
sure). I'm not sure what difference XP over Vista could possibly make.
Anyone else out there want to try them out? Just put some dates in A3
downward (not important how many), then make A1 the active cell and run
the line of code (you can so in the Immediate window if you want).

Rick


kounoike said:
Hi Rick

Your first formula doesn't work in my environment (Excel 2003, Xp SP2).
Escape sequence seems not to work for me.
Am i doing somthing wrong?
and i wonder how can i apply this method to format like below.
Dates is surrounded by quotes

"Dates:"2008 Feb 08 to 2008 Mar 02

i can't find the way to make this.

keiji

Rick Rothstein (MVP - VB) said:
You could have further obfuscated the statement by
hiding "Dates: " in the first format....

I didn't want to confuse the reader by doing that. <g>

But, as long as you insist <bg>, it could be done either this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""\Dat\e\s: dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

or this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""""""Dates: """"dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

Yes, either one of these is much better. <vbg>

Rick
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

Well, yes, for the code I tend to write at least. <g>

Rick


Jon Peltier said:
You mean, SOP.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Rick Rothstein (MVP - VB) said:
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


David said:
What is obfuscated? I am just a country boy.

:

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


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
_______


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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Coderre

It's usually best to avoid long words when a diminutive word will suffice.
<g>
(sorry....couldn't resist)
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Rick Rothstein (MVP - VB) said:
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


David said:
What is obfuscated? I am just a country boy.

Rick Rothstein (MVP - VB) said:
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


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
_______


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
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

But isn't the point of using the word "obfuscate" to obfuscate what you are
saying?<g>

Rick


Ron Coderre said:
It's usually best to avoid long words when a diminutive word will suffice.
<g>
(sorry....couldn't resist)
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Rick Rothstein (MVP - VB) said:
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


David said:
What is obfuscated? I am just a country boy.

:

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


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
_______


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Max of a range help 4
Need to turn this in to VBA 3
R[1]C[1] with a variable 2
VBA Code to select and format range 2
Have VBA/macro repeat process in Excel 4
Insert text in column A and calculate the average from column B 4
I'm trying to do a "count" function at the end of a dynamic pivot table 0
Macro to delete row if cells greater than certain value 1

Top