Posted to microsoft.public.excel.programming
|
|
Max character for each column.
Hi Dave
Perfect.
Thanks.
--
Best regards
Joergen Bondesen
"Dave Peterson" skrev i en meddelelse
...
Try:
L = Worksheets("Test").Evaluate("MAX(LEN(A1:A100))")
You don't need the leading = sign (but it won't hurt).
And Chip got enthusiastic with those double quotes <vbg (and I didn't
notice).
So you were really finding the length of this string:
A1:A100
Which is exactly 7 characters!
Joergen Bondesen wrote:
Hi Chip and Dave
Thank, but i do not work for me.
L = 7 Why ?
Do you have any suggestion?
I'm using Danish Excel 2007 and Danish Win XP
Info Len
abc 3
abc abc 8
abcdefghij 10
1232 4
Option Explicit
Sub test()
Dim L As Long
L = Worksheets("Test").Evaluate("=MAX(LEN(""A1:A100"") )")
MsgBox L
End Sub
L must be = 10
--
Best regards
Joergen Bondesen
"Chip Pearson" skrev i en meddelelse
...
Just to add to Chip's response...
I think I'd use this:
Good catch. You're absolutely correct.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Mon, 27 Oct 2008 18:33:37 -0500, Dave Peterson
wrote:
Just to add to Chip's response...
I think I'd use this:
L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")
Then I wouldn't have to worry about what worksheet was active when the
code ran.
Chip Pearson wrote:
You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,
Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:
Hi Alan
Thanks, it works fine, but I need a VBA solution. My mistake, sorry.
--
Dave Peterson
|