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

lb483

New Member
Joined
Sep 2, 2014
Messages
4
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Chris Mack

Well-known Member
Joined
Jun 18, 2013
Messages
803
Could you say:

Code:
Evaluate("VLOOKUP(DATE(YEAR(A57),MONTH(A57),2),Finance_Table," & Range("Expenditure").Column & ",FALSE)")
Hope this helps,

Chris.
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

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

lb483

New Member
Joined
Sep 2, 2014
Messages
4
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.
 
Upvote 0

Chris Mack

Well-known Member
Joined
Jun 18, 2013
Messages
803
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.
 
Upvote 0

lb483

New Member
Joined
Sep 2, 2014
Messages
4
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.
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The COLUMN function returns an array that apparently Evaluate can't dereference.
 
Upvote 0

Forum statistics

Threads
1,191,701
Messages
5,988,176
Members
440,135
Latest member
DCDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top