如何停止参考错误？

发布于 2025-01-20 18:04:17 字数 382 浏览 0 评论 0原文

=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")

您好，所以我对这个公式有问题。我有一个宏，可以删除IB Alps表上的一些数据。到宏完成时，所有引用的单元格/范围都已修复，但是中间的某个地方会创建一个参考错误 @ $ $ 59：$ k $ 1000（因为那个拆分第二细胞被删除）。我如何在宏具有的1秒停电时间内制止Excel编辑函数，或者如何编辑该功能以继续引用该范围，而无需立即添加参考。

原文

=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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

玩物 2025-01-27 18:04:17

如果您有能力编辑宏，则可以在删除单元格的同时暂时禁用表的计算：

ActiveWorkbook.Sheets("IB ALPS").EnableCalculation = False
' Some code that deletes rows goes here
ActiveWorkbook.Sheets("IB ALPS").EnableCalculation = True

这应该防止该工作表上的功能认为任何问题都出现了。请参阅 Microsoft文档更多信息。

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.

回复收藏 0 原文

~没有更多了~