View Full Version : Evaluate() to return column number

Digita

07-28-2009, 11:58 PM

Hi guys,

Just wondering why this statement

Debug.Print Evaluate("column(AC1)") errors out "Type mismatch". Thanks in advance for your input.

Regards

kp

I don't know, it is odd isn't it? I had expected it to work looking at it.

Is it important to do it this way, as there are alternatives?

Digita

07-29-2009, 12:34 AM

Hi Bob,

Well, just a curious question. As you already know, there are other ways to work out the column number such as: [ac1].column.

Cheers

kp

Digita

07-29-2009, 12:34 AM

Hi Bob,

Well, just a curious question. As you already know, there are other ways to work out the column number such as: [ac1].column.

Cheers

kp

Howdy guys,

I thought that .Column couldn't have parenthesis behind it?

Not that one would want to, but these seem ok.

MsgBox [AC:AC].Column

MsgBox Evaluate("AC:AC").Column

Mark

Mark,

He is trying to evaluate the worksheet command =COLUMN(AC1).

What you show would work, so would

Range("AC1").Column

but why does the original fail?

mikerickson

07-29-2009, 03:53 PM

This is great weirdness.

In Excel 2007

MsgBox Join(Evaluate("Column(ac1)")) returns "29"

On further investigation, it appears that Evaluate("Column(ac1)") returns a one element array.

Mark,

He is trying to evaluate the worksheet command =COLUMN(AC1).

ACK! I knew I was missing something Bob. Even stared at the question for a few before I ... footinmout

This is great weirdness.

In Excel 2007...

MsgBox Join(Evaluate("Column(ac1)"))

returns "29"

On further investigation, it appears that Evaluate("Column(ac1)") returns a one element array.

Hi Mike,

Returns same in 2003 (I'm at work now).

I checked out that with (more at my limited-knowledge perspective and trying to gain knowledge than anything) with:

a = Evaluate("Column(AC1)")

MsgBox a(1)

MsgBox Evaluate("Column(AC1)")(1)

...both returned 29.

Mark

mikerickson

07-29-2009, 07:01 PM

Hmm.. There is similar behaviour in Excel 2004

I note that

UBound(Evaluate("column(a1:d4)"), 1) & ":" & UBound(Evaluate("column(a1:d4)"), 2)

returns a subscript out of range error

UBound(Evaluate("column(a1:d4)"))

returns 4

and

UBound(Evaluate("row(a1:d4)"), 1) & ":" & UBound(Evaluate("row(a1:d4)"), 2)

returns 4:1

It looks like COLUMN returns a 1D array, while ROW returns a 2-D array.

This is consistant with entering ={1,2,3} in a cell, which, if entered as a non-array formula, it returns the only first element. If entered as an array formula, the full array is shown.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.