使用VBA编写公式时OR函数是否存在问题？

Question

由于我不理解的原因，我的vba宏卡在这一行上：

ActiveCell.Formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""),"",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""),"")"",""),A7))"

作为参考，原始手动输入的公式(语音符号较少且有效)为：

=IF(OR(A7="Account",AND(LEFT(A7,2)="ID",A8="Account"),A7=""),"",IF(RIGHT(A7,1)=")",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",100)),100)),"(",""),")",""),A7))

有什么建议吗?问题是什么？

我猜这是OR函数的一个问题，因为后面的几行都是没有问题的：

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),TRIM(MID(A7,4,FIND("" "",MID(A7,4,20)))),F6)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=B7-C7"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=TEXT(IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),C7,H6),""dd/mm/yyyy"")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=TEXT(IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),SUBSTITUTE(MID(MID(A7,FIND(""Posted by"",A7),99),FIND("" on "",MID(A7,FIND(""Posted by"",A7),99))+4,12),"")"",""""),I6),""dd/mm/yyyy"")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),IF(MID(A7,FIND(""Posted by "",A7)-1,1)=""("","""",MID(A7,FIND(""("",A7,FIND(""Posted by "",A7)-20)+1,FIND("": Posted by "",A7)-FIND(""("",A7,FIND(""Posted by "",A7)-20)-1)),J6)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),(MID(A7,FIND(""Posted by"",A7)+10,FIND("" on "",A7,FIND(""Posted by"",A7)+10)-FIND(""Posted by"",A7)-10)),K6)"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),MID(A7,FIND("" "",A7,4)+1,FIND(IF(MID(A7,FIND(""Posted by "",A7)-1,1)=""("",""(Posted by "",""(Manual Journal: Posted by ""),A7)-FIND("" "",A8,4)-2),M6)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Formula = "=IF(AND(LEFT(A7,2)=""ID"",A8=""Account""),RIGHT(A7,LEN(A7)-FIND("")"",A7,FIND(""Posted by "",A7))-1),N6)"
ActiveCell.Offset(0, -9).Range("A1").Select

Answer 1

你需要加倍所有的报价。一些遗漏了：

这意味着如果您的原始公式有类似… A7="" …的内容，则需要在"… A7="""" …"这样的字符串中将它们加倍。

变化

ActiveCell.Formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""),"",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""),"")"",""),A7))"

转到

ActiveCell.Formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""""),"""",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""""),"")"",""""),A7))"

它将得到这个公式：

=IF(OR(A7="Account",AND(LEFT(A7,2)="ID",A8="Account"),A7=""),"",IF(RIGHT(A7,1)=")",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",100)),100)),"(",""),")",""),A7))

Answer 2

基本上，你的错误是，如果你想告诉公式使用空字符串，你需要在VBA代码中放入"""" (例如A7="""")。双引号告诉VBA编译器将一个引号字符放入字符串中，因此对于2个引号字符，您需要2*2个引号(假设您的公式是正确的，那么您就有4个这样的实例)。

建议:将to formula写入一个变量，并将该变量赋给单元格的formula-property。这样，您就可以很容易地使用调试器进行检查：

Dim formula as String
formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""),"",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""),"")"",""),A7))"
Debug.Print formula

这将揭示问题-即时窗口中的输出为：

=IF(OR(A7="Account",AND(LEFT(A7,2)="ID",A8="Account"),A7="),",IF(RIGHT(A7,1)=")",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",100)),100)),"(","),")","),A7))`

你需要的是

formula = "=IF(OR(A7=""Account"",AND(LEFT(A7,2)=""ID"",A8=""Account""),A7=""""),"""",IF(RIGHT(A7,1)="")"",SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A7,"" "",REPT("" "",100)),100)),""("",""""),"")"",""""),A7))"