# Dynamic calculation in VBA

#### Taihenwarui

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### oldbrewer

##### Board Regular
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?

1,101,928
Messages
5,483,748
Members
407,408
Latest member
chandika

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...