'How can I stop REF errors?
=IFERROR(VLOOKUP("Processed Units Forecast",'IB ALPS'!$A$59:$K$1000,MATCH(Date1,INDEX('IB ALPS'!$1:$1048576,3,1):INDEX('IB ALPS'!$1:$1048576,3,11),0),FALSE),"N/A")
Hello, so I'm having an issue with this formula. I have a macro that deletes some of the DATA on the IB ALPS sheet. By the time the macro is finished, all of the referenced cells/ranges are fixed, but somewhere in the middle it creates a REF error @ $A$59:$K$1000(because of that split second where the cells are deleted). How can I stop excel from editing the function in that 1 second down time that the macro has, or how can I edit the function to keep referring to that range without immediately adding a REF.
Solution 1:[1]
If you have the ability to edit the macro, you can temporarily disable calculation for your sheet while deleting the cells:
ActiveWorkbook.Sheets("IB ALPS").EnableCalculation = False
' Some code that deletes rows goes here
ActiveWorkbook.Sheets("IB ALPS").EnableCalculation = True
This should prevent the functions on that sheet from thinking that anything has gone wrong. See the Microsoft documentation on this property for more information.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | plentyofcoffee |