修改后编译错误

Question

我试图修改一个spreadhseet，因为它似乎最终会出现一些错误。下面 的代码是当前之一：

Range("DP2").Value = "=IF(RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)<0,60+RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2),RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)) + RC[-1]" 

的问题是，该逻辑在看的值是时间（小时，分钟），它需要正确的2（MM）和从另一时间减去（MM），如果价值低于零，它只是增加60，假设第一次提前一小时（这是事实），但问题是我观察到差异超过一小时（2小时，3小时等）例如1110和0830，因此只加60就不会做正确的计算。 我写这段代码：

IF(RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)<=0, IF(LEFT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-LEFT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)=1,60+RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2), IF(LEFT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-LEFT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)=2,120+RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2), IF(LEFT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-LEFT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)=3,180+RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2),RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2))))) + RC[-1]" 

这一个应该看（左2日，HH）的时间值的小时部分，并据此决定是否增加60，120或180 但VBA不会接受代码，说这是一个编译错误！

Answer 1

你已经超过了VBE每行的字符数，打破了你的字符串在单独的行，像这样：

Range("D2").Value = "IF(RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]" & _ 
"=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)<=0, IF(LEFT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-LEFT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)=1,60+RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2), IF(LEFT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-LEFT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)=2,120+RIGHT(IF" & _ 
"(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP" & _ 
"(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2), IF(LEFT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-LEFT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2)=3,180+RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2),RIGHT(IF(R1C[-110]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-110],0)),2)-RIGHT(IF(R1C[-111]=RC7,VLOOKUP(RC4,Services!C1:C254,32,0),VLOOKUP(RC4,Services!C1:C254,146+R1C[-111],0)),2))" & _ 
"))) + RC[-1]"