I'm getting a #VALUE error in a formula when running a macro... can anyone help?
About the file: very large, lots of data, a dashboard page spits out returns and a detailed overview of a given asset...
About the macro: it cycles through assets on the dashboard page and essentially takes a screenshot of the dashboard's values and uploads them into a new tab...
About the macro problem: when we cycle through a given asset on the dashboard, all cells/formulas seem to work... it is only when we run the macro that we gain an error on a formula that triggers the IF TRUE portion...
Snippet of macro that we run:
...Repeat until End Sub...Please Login or Register to view this content.
Formula that returns a #VALUE error:
'=+IF(INDEX('Reversion Dashboard'!$I:$I,MATCH($D$3,'Reversion Dashboard'!$C:$C,0))=1,I331*(1+(RATE($E$325,,$I331,-('Reversion Schedule'!$K$19)))),"")
About the formula: the INDEX MATCH is simple, when an asset is listed as a 1, it is supposed to calculate the formula... but unfortunately, it does not... the 'Reversion Schedule'!$K$19 cell is also a complicated formula (obviously in another worksheet)...
About the solution: how do we ensure that the formula does not show a #VALUE when triggered by a listed 1 via the INDEX MATCH? It seems to only work when we slowly cycle through the dashboard, thereby giving the formulas enough time to iterate and populate the line with correct info... that said, I added a .WAIT function to the macro which did not change anything, except make my macro take longer...
Can anyone help me succeed in making this sucka calculate?
THANK YOU IN ADVANCE!!
Bookmarks