如果 vba 宏中的错误和 vlookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14077458/
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
if error and vlookup in vba macros
提问by PASUMPON V N
My task to write a formula which includes if, iferror and Vlookup
我的任务是编写一个包含 if、iferror 和 Vlookup 的公式
I need to apply the formula till the end of last row which contains values .
我需要将公式应用到包含 values 的最后一行的末尾。
if vlookup throws NA , then the cell should be blank , othewise the value should be replaced with numerical Value "1"
如果 vlookup 抛出 NA ，则单元格应为空白，否则该值应替换为数值“1”
but when tried formula but it is giving error
但是当尝试公式时却出现错误
Sub testt()
Dim l As Long
l = Sheets(1).Range("A1:A" & Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row).Count
With Sheets("Sheet1")
.Range("d1").Formula = "=IF(iferror(vlookup(c2,$D:$D,1,false),""),"",1)"
.Range("d1").AutoFill Destination:=Range("d1:d" & l), Type:=xlFillDefault
End With
End Sub
采纳答案by CuberChase
I think the problem is with the IF part of the formula, in particular the logical test. For a spreadsheet IF function the formula is:
我认为问题在于公式的 IF 部分，尤其是逻辑测试。对于电子表格 IF 函数，公式为：
=IF(logical_test, value_if_true, value_if_false)
The logical test is (from the Excel help file) any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
逻辑测试是（来自 Excel 帮助文件）可以评估为 TRUE 或 FALSE 的任何值或表达式。例如，A10=100 是一个逻辑表达式；如果单元格 A10 中的值等于 100，则表达式的计算结果为 TRUE。否则，表达式的计算结果为 FALSE。
Your logical test is simply:
您的逻辑测试很简单：
iferror(vlookup(c2,$D:$D,1,false),"")
Perhaps it should be:
也许应该是：
iferror(vlookup(c2,$D:$D,1,false),"") = ""
Giving the formula (with 'The King's suggestion too):
给出公式（还有“国王的建议”）：
"=IF(iferror(vlookup(c2,$D:$D,1,false),"""")="","",1)"
回答by The King
My first thought... in VBA you should use two " for every " in your formula...
我的第一个想法......在VBA中你应该在你的公式中使用两个“ for each”......
Also, do you want to use IsError.
另外，您想使用 IsError.
I would replace the formula as below
我会替换下面的公式
.Range("d1").Formula = "=IF(iserror(vlookup(c2,$D:$D,1,false)),"""",1)"
Otherwise, please let us know, what error you are getting.
否则，请让我们知道您遇到了什么错误。
