VBA语法-带有IF语句的Vlookup

Question

我有两个vba查询语法，

语法1

activeRange.Value = "=VLOOKUP(IFNA(IFNA(IFNA(IFNA(VLOOKUP(LEFT(RC[-1],6),'List Sheet'!R2C1:R18C2,2,FALSE),VLOOKUP(RIGHT(RC[-1],5),'List Sheet'!R2C1:R18C2,2,FALSE)),VLOOKUP(RIGHT(RC[-1],2),'List Sheet'!R2C1:R18C2,2,FALSE)),IF(OR(LEFT(RC[-1],2)=""EZ"",LEFT(RC[-1],3)=""STO""),""STORE - ORDER"",NA())),IFNA(VLOOKUP(RIGHT(RC[-1],6),'List Sheet'!R2C1:R18C2,2,FALSE),IFNA(VLOOKUP(LEFT(RC[-1],3)," & _
"'List Sheet'!R2C1:R18C2,2,FALSE),""CUSTOMER ORDER""))),'List Sheet'!R2C8:R16C9,2,FALSE)" & _
""

语法2(请注意，公式在vba中的一行中，而不是在这里表示的多行中(

activeRange.Value = "=VLOOKUP(IFNA(IFNA(IFNA(IFNA(VLOOKUP(LEFT(RC[-1],6),'List Sheet'!R2C1:R18C2,2,FALSE),VLOOKUP(RIGHT(RC[-1],5),'List Sheet'!R2C1:R18C2,2,FALSE)),VLOOKUP(RIGHT(RC[-1],2),'List Sheet'!R2C1:R18C2,2,FALSE)),IF(OR(LEFT(RC[-1],2)=""EZ"",LEFT(RC[-1],3)=""STO""),""STORE - ORDER"",NA())),IFNA(VLOOKUP(RIGHT(RC[-1],6),'List Sheet'!R2C1:R18C2,2,FALSE),IFNA(VLOOKUP(LEFT(RC[-1],3),
"'List Sheet'!R2C1:R18C2,2,FALSE),""CUSTOMER ORDER""))),'List Sheet'!R2C8:R16C9,2,FALSE)"""

两个代码都是相同的，但vba接受第一个，第二个总是出错错误为"strong"；应用程序定义错误或对象定义错误">

我是如何获得语法1的->我用宏录制的

我是如何获得语法2的->我试着自己写。

为什么会发生错误？？请帮我理解

Answer 1

尝试

activeRange.Value = "=VLOOKUP(IFNA(IFNA(IFNA(IFNA(VLOOKUP(LEFT(RC[-1],6)," & _
"'List Sheet'!R2C1:R18C2,2,FALSE),VLOOKUP(RIGHT(RC[-1],5)," & _
"'List Sheet'!R2C1:R18C2,2,FALSE)),VLOOKUP(RIGHT(RC[-1],2)," & _
"'List Sheet'!R2C1:R18C2,2,FALSE))," & _
"IF(OR(LEFT(RC[-1],2)=""EZ"",LEFT(RC[-1],3)=""STO""),""STORE - ORDER"",NA()))," & _
"IFNA(VLOOKUP(RIGHT(RC[-1],6)," & _
"'List Sheet'!R2C1:R18C2,2,FALSE),IFNA(VLOOKUP(LEFT(RC[-1],3)," & _
"'List Sheet'!R2C1:R18C2,2,FALSE),""CUSTOMER ORDER"")))," & _
"'List Sheet'!R2C8:R16C9,2,FALSE)"