Dynamic calculation in VBA

Taihenwarui

New Member
Joined
Aug 21, 2015
Messages
1
Hi. I'm a very new to Excel and VBA
I have been given a task to improve and automate the complex engineering calculation spreadsheet.

The problem is to replace a hidden table values ( which are needed for a final calculation calculation) with on the fly , dynamic calculation ( more elegant way as by boss described it ) in VBA and macro.

The formula for the final cell is

Code:
=IF(AS11="yes",IF(NOT(ISERROR(SUM(AV11,AX11,AZ11,BB11,BD11,BF11,BH11,BJ11,BL11,BN11))),SUM(AV11,AX11,AZ11,BB11,BD11,BF11,BH11,BJ11,BL11,BN11),0),"")
where AV11,AX11,AZ11,BB11,BD11,BF11,BH11,BJ11,BL11,BN11 are the hidden columns made for a calculation of the final value.

Its getting more complicated as the number of hidden columns depends of another factor . Here is the formula for a hidden column

Code:
=IF(AW11>0,INDEX($AR$11:$AR$24,AW11),0)
The column value depends of the another factor . In this case it depends of the value of AW11 cell.
The formula for AW11

Code:
=IF(ISERROR(IF(MATCH(A11,$H$11:$H$24,0)>0,MATCH(A11,$H$11:$H$24,0),MATCH(A11,$J$11:$J$24,0))),0,IF(MATCH(A11,$H$11:$H$24,0)>0,MATCH(A11,$H$11:$H$24,0),MATCH(A11,$J$11:$J$24,0)))

And so on. So with the grow of the calculation the number of hidden column can be bigger. It can be one or five.

My questions is if anyone has ever encountered such a problem and if this is feasible to make such a calculation in VBA (trigger by the event) to replace getting values from a column by calculation using marco and vba?

Thank you
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,997
It is sometimes better to knock the house down completely and rebuild it rather than keep on building new bits

I would be happy to assist with that, but regret I do not understand your needs. Words are fine to you because you are very familiar with the spreadsheet, we are not

Why are columns hidden, is it security or aesthetics or something else?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,013
Members
406,627
Latest member
IncandenzaH1997

This Week's Hot Topics

Top