Running VBA causes a working, successful function to throw a #VALUE error

meditated

New Member
Joined
Dec 17, 2019
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hello all, I'm getting a #VALUE error when running a new macro. This error is killing my entire model. The function is successful outside of running the macro - so I am very confused why it breaks only when running the macro... could this be an iterating problem?

Formula:
=+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)))),"")

Macro:
VBA Code:
' Cycle/calc all assets then create new worksheets and paste formulas as values macro
Sheets("Dashboard").Copy Before:=Sheets(1)
Range("D3").FormulaR1C1 = "Asset #1"
Application.CalculateFull
If Not Application.CalculationState = xlDone Then DoEvents
Range("A1:XFD1000").Value = Range("A1:XFD1000").Value

Sheets("Dashboard").Copy Before:=Sheets(1)
Range("D3").FormulaR1C1 = "Asset #2"
Application.CalculateFull
If Not Application.CalculationState = xlDone Then DoEvents
Range("A1:XFD1000").Value = Range("A1:XFD1000").Value

Sheets("Dashboard").Copy Before:=Sheets(1)
Range("D3").FormulaR1C1 = "Asset #3"
Application.CalculateFull
If Not Application.CalculationState = xlDone Then DoEvents
Range("A1:XFD1000").Value = Range("A1:XFD1000").Value

//'...Repeat... 

End Sub


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...
About the formula: The INDEX MATCH is simple, when an asset is listed as a 1, it is supposed to calculate the formula. 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, if possibly 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 help.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,112,817
Messages
5,542,670
Members
410,567
Latest member
SCraig123
Top