vba excel中宏的iferror语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16659605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
iferror statement for macros in vba excel
提问by Sean Connecticut
I have a macro that at one point creates a pivot table. My problem is sometimes not all the values are in the second column and I get an error.
我有一个宏，它一度创建了一个数据透视表。我的问题有时不是所有的值都在第二列中，我得到一个错误。
I figured a way around this by getting the values from the column to the left which are the same. But with this method if the pivots are the same I get an "error no special cells found".
我找到了一种解决方法，方法是从左侧的列中获取相同的值。但是使用这种方法，如果枢轴相同，我会收到“未找到特殊单元格的错误”。
Sorry let me be more clear I have a column A for contract names and column C is a pivot that brings up the values of the contract from another table. Where I am getting the error is column B which has an if statement which basically says if column C is "yes" then use contract names. I still want the value if my if statement in column B produces an error
抱歉，让我更清楚一点，我有一个用于合同名称的列 A，而列 C 是一个从另一个表中调出合同值的数据透视表。我得到错误的地方是 B 列，它有一个 if 语句，基本上说如果 C 列是“是”，那么使用合同名称。如果 B 列中的 if 语句产生错误，我仍然需要该值
What I want is something along the lines of
我想要的是类似的东西
For each cl in range("C1:C200")
if error and cl.offset(0, 1).value = yes then
cl.value = cl.offste(0, -1) Else
Cl.value = cl.value
End if
回答by user2140261
I am very confused by your question and have a strong feeling there is a MUCH better solution to your problem but I am unsure what exactly your problem is, so here is a working version of your code:
我对您的问题感到非常困惑，并且强烈认为您的问题有更好的解决方案，但我不确定您的问题究竟是什么，所以这是您的代码的工作版本：
For each cl in range("C1:C200")
If IsError(cl) and cl.offset(0, 1).value = "yes" then
cl.value = cl.offset(0, -1)
End If
Next cl
Or this might be more what you want: only look at the errors and use the value to the left if the value to the right is yes?
或者这可能更符合您的要求：如果右侧的值为yes? ，则仅查看错误并使用左侧的值？
Sub error()
Dim rngErrors As Range
On Error Resume Next
Set rngErrors = Range("C1:C200").SpecialCells(xlCellTypeFormulas, xlErrors)
For Each rngcError In rngErrors
If cl.offset(0, 1).value = "yes" then
cl.value = cl.offset(0, -1)
End If
Next rngcError
On Error GoTo 0
End Sub
there is no need for your original elseas if the Ifstatement is not met nothing will happen to it anyway. Unless there is a formula in the cell you would like to get rid of.
不需要您的原件else，就好像If没有满足该语句一样，无论如何它都不会发生任何事情。除非您想删除单元格中的公式。
