根据VBA中的公式误差/值设置变量

I am trying to calculate a sheet (set on manual calcs) continuously until there are no more errors. I have the following code below, but it appears that it does not recognize the variables I set, as the values for those variables are the result of a formula and not actually what is in the cell as a value. I saw another question on a somewhat related topic that used a worksheet function, but have been unable to see how I can fit it into my code. Usually if I run this process without VBA code, I do the manual calcs 5-6 times, but for the code I would like to be more accomodating. The rest of my code works, it is just this part that is messing up the process. I suppose I could just calc the sheet like I normally would on a loop for some arbitrary number of calcs, but if possible I would like it to calc only if the two errors below are present. See below for piece of code.

我一直在尝试计算一个表(在手动calcs上设置)，直到不再有错误。下面有下面的代码，但它似乎不认识我设置的变量，因为这些变量的值是公式的结果，而不是单元中的值。我看到了另一个关于使用工作表函数的相关主题的问题，但是我不知道如何将它放入代码中。通常，如果我在没有VBA代码的情况下运行这个过程，我会做5-6次手工calcs，但是对于代码，我希望更适合。我剩下的代码可以工作了，就是这部分把整个过程搞砸了。我想我可以像往常一样，在一个循环中为任意数量的calcs做一个循环，但是如果可能的话，只有在下面两个错误出现时，我才会喜欢它。请参见下面的代码片段。

  Dim n As Variant
  Dim na As Variant
  n = "#N/A Requesting Data..."
  na = "#N/A Invalid Override"

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  For Each Cell In Range("AZ13:BQ82")
      If Cell.Value = n Then
          Sheet1.Calculate
      End If
  Next

  For Each Cell In Range("AZ13:BQ82")
      If Cell.Value = na Then
          Sheet1.Calculate
      End If
  Next

1 个解决方案

#1

IsError is your friend

返回错误是你的朋友

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  For Each Cell In Range("AZ13:BQ82")
      If IsError(Cell.Value) Then
          Sheet1.Calculate
      End If
  Next