VBA - 缩短重复的公式分配

Question

我想缩短我非常重复的代码，其中有很多这样的行:

.Range("AB81") = "=VLOOKUP(I65,'[" & R1 & "]" & "Investments" & "'! $B$58:$F$81," & 5 & ",0)"
.Range("AD81") = "=VLOOKUP(K65,'[" & R1 & "]" & "Investments" & "'! $B$58:$F$81," & 5 & ",0)"
.Range("AF81") = "=VLOOKUP(M65,'[" & R1 & "]" & "Investments" & "'! $B$58:$F$81," & 5 & ",0)"
.Range("AH81") = "=VLOOKUP(O65,'[" & R1 & "]" & "Investments" & "'! $B$58:$F$81," & 5 & ",0)"
.Range("AJ81") = "=VLOOKUP(Q65,'[" & R1 & "]" & "Investments" & "'! $B$58:$F$81," & 5 & ",0)"
.Range("AL81") = "=VLOOKUP(S65,'[" & R1 & "]" & "Investments" & "'! $B$58:$F$81," & 5 & ",0)"

.Range(..81) 范围每次移动 2 列，VLOOKUP 公式中的第一个范围也是如此。

Answer 1

一次设置所有公式时，相对引用会自动调整:

.Range("AB81,AD81,AF81,AH81,AJ81,AL81").Formula = _
    "=VLOOKUP(I65,'[" & R1 & "]" & "Investments" & "'!$B$58:$F$81," & 5 & ",0)"