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.
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.