为什么在多行代码中出现VBA语法错误? [英] Why is there a VBA syntax error in this multi-line of code?
本文介绍了为什么在多行代码中出现VBA语法错误?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这么长的代码行. VBE说它有语法错误.我检查了代码,我很确定它应该没问题.我超出了限制吗?
I have a line of code that is this long. VBE says it has syntax error. I checked the code and i'm pretty sure it should be fine. Did I exceed the limit?
Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 26) = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",0,IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0)),INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))+(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ") _
+2*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*0.3*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*1.3*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*1.3*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0 _
,0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.5+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.5*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.5*0.1),0)),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0 _
,INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0)),INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))+(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*1.3 _
+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.3+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.3*1.3 _
+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.3*0.1),0))))"
推荐答案
尝试以下语句,以确保在字符串文字中不会出现换行符:
Try the following statement, which ensures that line breaks do not occur within string literals:
Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 26) = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",0,IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0)),INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))+(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")" & _
"+2*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*0.3*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*1.3*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*1.3*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0" & _
",0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.5+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.5*1.3+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.5*0.1),0)),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""Regular"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0" & _
",INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0)),INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))+(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*1.3" & _
"+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*2*0.1),IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(DTR!C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=""SNWH"",IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")=0,0,(DTR!P" & Total_Rows_Formulas + 1 & "+DTR!R" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.3+(DTR!Q" & Total_Rows_Formulas + 1 & "+DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.3*1.3" & _
"+(DTR!R" & Total_Rows_Formulas + 1 & "+1.3*DTR!S" & Total_Rows_Formulas + 1 & ")*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8)*1.3*0.1),0))))"
我不知道代码是否会真正运行,但至少可以避免语法错误.
I have no idea whether the code will actually run, but it at least gets around the syntax error.
这篇关于为什么在多行代码中出现VBA语法错误?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文