Evaluate() to return column number

[Digita]

Hi guys,

Just wondering why this statement
Debug.Print Evaluate("column(AC1)") errors out "Type mismatch". Thanks in advance for your input.

Regards


kp

[Bob Phillips]

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]

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]

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

[GTO]

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

[Bob Phillips]

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]

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.

[GTO]

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]

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.