如果您输入以下代码,则以下代码将起作用Code
值在sheet2
并突出显示它们,然后运行此宏:
Selection.Offset(0, 1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Sheet1!C[-1]:C,2,FALSE),"""")"
Selection.Offset(0, 2).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet1!C[-2]:C,3,FALSE),"""")"
Selection.Offset(0, 1).Value = Selection.Offset(0, 1).Value
Selection.Offset(0, 2).Value = Selection.Offset(0, 2).Value
Edit:如果您想在键入时更新值,请使用(感谢@PeterAlbert 的额外优化!):
Private Sub Worksheet_Change(ByVal Target As Range)
'end if the user made a change to more than one cell at once?
If Target.Count > 1 Then End
'stop system activating worksheet_change event while changing the sheet
Application.EnableEvents = False
'continue if column 1(A) was updated
'and
'dont continue if header or row 1 was changed
If Target.Column = 1 And Target.Row <> 1 Then
With Target.Offset(0, 1) 'alter the next cell, current column +1 (column B)
'RC1 = current row and column 1(A) e.g. if A2 was edited, RC1 = $B2
'C1:C2 = $A:$B
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sheet1!C1:C2,2,FALSE),"""")"
.Value = .Value 'store value
End With
With Target.Offset(0, 2) 'alter the next cell, current column +2 (column C)
'C1:C3 = $A:$C
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sheet1!C1:C3,3,FALSE),"""")"
.Value = .Value 'store value
End With
End If
Application.EnableEvents = True 'reset system events
End Sub
RC的解释:
The FormulaR1C1
当引用当前单元格的单元格时,可以很好地使用公式类型。有一些规则需要记住:
- The
R
代表行和C
用于 Column 及其后面的整数(如果有)定义行或列;
- 作为基础
RC
公式引用自身;
- 后面的任何数字
R
or C
包装成[]
是其自身的偏移量,例如如果你在牢房里A1
并使用R[1]C[1]
你会引用单元格B2
;
- 也可以是以下任意数字
R
and C
是一个精确的,例如如果你参考R2C2
无论您所在的单元格也会指向B2
; and
如果你在牢房里,事情就会变得复杂C5
,例如使用Range("C5").FormulaR1C1 =
并编码如下:
-
"=RC[-1]"
参考单元格B5
-
"=RC1"
参考单元格A5
,更正确的是$A5
-
"=R[1]C[-2]"
参考单元格A6
-
"=Sum(C[-1]:C5)"
is =Sum(B:E)
,更正确的是=Sum(B:$E)