VLOOKUP公式将撇号放在文本前面,并生成运行时错误"1004" [英] VLOOKUP Formula places Apostrophe in front of text and generates Run-time error '1004'
问题描述
我正在编写一个将一些公式插入列数组的宏.我正在尝试使每周流程自动化,因此输入文件是可变的,并且是通过 msoFileDiaglogFilePicker
应用程序选择的.
I am writing a macro which inserts some formulas into a column array. I am trying to automate a weekly process, so the input files are variable and are selected through the msoFileDiaglogFilePicker
application.
Dim wb_Final As Workbook, nameFinal As String
Set wb_Final = Workbooks.Open(Filename:=Final_Directory)
nameFinal = wb_Final.Name
Dim wb_Summary As Workbook, nameSummary As String
Set wb_Summary = Workbooks.Open(Filename:=Summary_Directory)
nameSummary = wb_Summary.Name
wb_Summary.Sheets("Sheet 1").Activate
With Sheets("Sheet 1")
.Range("AT4:AT5000").Formula = "=IF(OR(AX1=""Open"",AX1=""Won"",AX1=""Won - Pending""),""Yes"",""No"")"
.Range("AU4:AU5000").Formula = "=VLOOKUP(W:W,LOVs!H:I,2,FALSE)"
.Range("AV4:AV5000").Formula = "=IF(IFERROR(VLOOKUP($A:$A,'[" & nameFinal & "]DATA'!$A:$AK,34,FALSE),0)=0,"",VLOOKUP($A:$A,'[" & nameFinal & "]DATA'!$A:$AK,34,FALSE))"
.Range("AW4:AW5000").Formula = "=IF(IFERROR(VLOOKUP($AV:$AV,'[" & nameFinal & "]DATA'!$AH:$CX,48,FALSE),0)=0,"",VLOOKUP($AV:$AV,'[" & nameFinal & "]DATA'!$AH:$CX,48,FALSE))"
'....More formulas similar to above
End With
将前两个公式放到单元格中,可以毫无问题地进行计算.
The first two formulas get placed into the cells and compute with no problem.
在excel中,第三个公式作为文本放置在单元格中,并在其前面带有撇号.(即'= IF(IFERROR(VLOOKUP ...
)
The third formula gets placed into the cells as a text with an apostrophe in the front of it in excel. (i.e. '=IF(IFERROR(VLOOKUP...
)
第四个公式生成运行时错误'1004'
我尝试了所有不同的公式类型:
I have tried all the different formula types:
.Formula.FormulaR1C1.FormulaLocal.FormulaR1C1Local
并且仍然收到相同的错误.
And still receive the same error.
我认为我遇到了中所述的类似问题本文,但是如果我可以使用 Application.Vlookup
函数而无需在当前的VLOOKUP函数中重新定义所有数组和列引用的话(这将花费很长时间),我不是.
I think I am experiencing a similar issue as stated in this article, but I'm not if I can use the Application.Vlookup
function without redefining all the arrays and column references in my current VLOOKUP functions (which would take a VERY long time).
任何帮助将不胜感激
推荐答案
尝试
With Worksheets("Sheet 1") '>== or Sheet1 depending on actual name
.Range("AW4:AW5000").Formula = "=IF(IFERROR(VLOOKUP($AV:$AV,[" & nameFinal & "]DATA!$AH:$CX,48,FALSE),0)=0,"""",VLOOKUP($AV:$AV,[" & nameFinal & "]DATA!$AH:$CX,48,FALSE))"
End With
这篇关于VLOOKUP公式将撇号放在文本前面,并生成运行时错误"1004"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!