Excel公式

Question

所以，在Sheet1我有一些名称的基础，它看起来像这样：

在Sheet2中，我从工作表Sheet1这些名称的工作。我这样做是为了在列A中输入代码值并在列B中输入名称，在列C中输入姓氏。这看起来是这样的：

我做这个用的公式，在公式编辑栏中输入密码。对于列A（或名称），我已经使用了这个公式：=IFERROR(VLOOKUP(A2;Sheet1!A:C;2;FALSE);"")和列B（或姓氏）我已经使用这个：=IFERROR(VLOOKUP(A2;Sheet1!A:C;3;FALSE);"")。我已将这些公式拖到第20行，并且效果很好。

现在，我想要做的就是将这些公式放入Excel VBA代码中，并将它们用于指定的范围。我刚开始使用VBA，我不知道如何去做，尝试了一些东西，但没有奏效，......我到目前为止已经做到了这一点。我是新来的这个Excel /宏/ VBA的东西，所以任何帮助，将不胜感激。

Answer 1

如果您在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 

编辑：如果你想要在键入时使用更新的值（谢谢@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 

Explinatioin RC的：

FormulaR1C1公式类型在引用与当前单元格相关的单元格时很适用。有一些规则要记住：

• 的R代表行和C是柱和它，如果有的话，定义了行或列后的整数;
• 作为RC公式引用的基础;
• 中包含的R或C之后的任何数字都是自身的偏移量，例如，如果您位于单元格A1中并使用R[1]C[1]，那么您将引用单元格B2;
• 此外，R和C之后的任何数字都是确切的，例如，如果您参考R2C2，则无论您所在的单元格是否也指向B2;和

如果您在单元格C5（例如，使用Range("C5").FormulaR1C1 =和编码的follwing：

1. "=RC[-1]"引用细胞B5
2. "=RC1"引用细胞A5，更正确地$A5
3. "=R[1]C[-2]"引用细胞A6
4. "=Sum(C[-1]:C5)"是=Sum(B:E)，更正确地=Sum(B:$E)

Answer 2

如果我正确理解您的问题和意见，您希望确保列B & C始终根据您的公式向您显示正确的值，但也希望保护（甚至可能隐藏公式）给用户。

我建议您使用表保护，而不是：您只需解锁希望用户编辑的单元格，即选择列A并在_格式单元格_对话框中取消选中“锁定”保护选项卡。同样，对于列B & C，请选中“隐藏”。现在右键单击表名并选择保护表。完成此操作后，用户可以编辑列A - 但不会在B & C中看到公式，并且无法编辑这些单元格。

如果您需要确保这在VBA一些原因，使用下面的代码：

Private Sub Worksheet_Change(ByVal Target As Range) 
    Application.EnableEvents = False 'to prevent endless loop 
    With Target.Offset(, 2 - Target.Column).Resize(, 2) 
     .FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sheet1!C1:C3,COLUMN(RC),0),"""")" 
     .Value = .Value 
    End With 
    Application.EnableEvents = True 
End Sub 

您需要将这个工作表的模块中。