VBA: Using COLUMN() or ROW() within EVALUATE()

[lb483]

I have a spreadsheet containing various formulas and I have a requirement to process parts of it using Excel. One of the issues I've recently encountered is using the COLUMN() function and then trying to EVALUATE() the formula within VBA.


The specific formula is:


=VLOOKUP(DATE(YEAR(A57),MONTH(A57),2),Finance_Table,COLUMN(Expenditure),FALSE)


In the spreadsheet, this is fine. However in VBA I get a Type Mismatch if I parse it through EVALUATE(). If I replace "COLUMN(Expenditure)" with 7 - which is the column for this named cell - it works fine but the requirement of this VLOOKUP() is that it must return data from the "Expenditure" column even if columns are moved around.


I know in VBA I could change this to Range("Expenditure").Column but the formula being evaluated must be that contained in the cell - so the code has to work in the spreadsheet itself.


Can anyone advise how to make COLUMN() work within EVALUATE()? I have a workaround by creating my own COLUMN() function but this seems excessive. Is the native COLUMN() function returning something other than a number and if so, can it be coerced into an integer using a method supported by VBA and the spreadsheet?


Any help greatly appreciated.

 

[Chris Mack]

Could you say:

Evaluate("VLOOKUP(DATE(YEAR(A57),MONTH(A57),2),Finance_Table," & Range("Expenditure").Column & ",FALSE)")

Hope this helps,

Chris.

 

[Andrew Poulsom]

Try:

Evaluate("=VLOOKUP(DATE(YEAR(A57),MONTH(A57),2),Finance_Table,COLUMNS(INDEX(Finance_Table,1,1):Expenditure),FALSE)")

 

[lb483]

Chris - That works in the VBA but since the same formula has to exist in the spreadsheet I can't use it in my solution.

Andrew - That works great! However it spawns the follow-up questions "Why does this work?" and "What is the problem with COLUMN() that this method addresses?". Thanks.

 

[Chris Mack]

What do you mean by "has to exist in the spreadsheet"? I tried inputting a formula with VBA using "COLUMN(Expenditure)" and it worked fine.

Chris.

 

[lb483]

Chris.

What at I meant was, if you tried to put the content of the EVALUATE() in a cell in the spreadsheet as:

=VLOOKUP(DATE(YEAR(A57),MONTH(A57),2),Finance_Table," & Range("Expenditure").Column & ",FALSE)

it will fail. The EVALUATE() is running in a macro and using the values in a cell to auto-populate a comment box. So if I have a cell containing:

=100.99+37.5+VLOOKUP(DATE(YEAR(A57),MONTH(A57),2),Finance_Table,COLUMN(Expenditure),FALSE)

then I'll get three rows in my comments box for:

£100.99
£37.50
Whatever the result of my VLOOKUP() is - hence the use of EVALUATE()

Because I'm using this method, the formula must be valid for a cell as well as for EVALUATE().

I hope oped that makes sense.

 

[Andrew Poulsom]

The COLUMN function returns an array that apparently Evaluate can't dereference.

 

[Chris Mack]

Ah, so it's an array, I see.

Thanks.

 

[lb483]

Thanks again, Andrew