Question

我想用VBA的formula.R1C1的格式来表现如下的公式，求解

=IF(LEFT(B9,3)="CRE",VLOOKUP(A9+1,Sheet2!A:B,2,0),IF(ISNA(VLOOKUP(JDE!D9,Sheet2!D:D,1,0)),"",VLOOKUP(JDE!D9,Sheet2!D:D,1,0)))

Answer 1

直接用 range.formula好了,干嘛非得formular1c1?

range("x9").formula="IF(LEFT(B9,3)=""CRE"",VLOOKUP(A9+1,Sheet2!A:B,2,0),IF(ISNA(VLOOKUP(JDE!D9,Sheet2!D:D,1,0)),"""",VLOOKUP(JDE!D9,Sheet2!D:D,1,0)))"

或者你录个宏,录制的宏里的公式就是r1c1格式的.
"=IF(LEFT(R[3]C[-8],3)=""CRE"",VLOOKUP(R[3]C[-9]+1,Sheet2!C[-9]:C[-8],2,0),IF(ISNA(VLOOKUP(JDE!R[3]C[-6],Sheet2!C[-6],1,0)),"""",VLOOKUP(JDE!R[3]C[-6],Sheet2!C[-6],1,0)))"

追问

我是需要在E列一整列都需要把这个公式拖到底，是不是只要把range("x9")改成range("Ex")?

追答

range("e9:e100")