vba VBA代码中的Excel公式

Excel formula in VBA code

提问by Sylca

So, in Sheet1 I have base of some names and it looks like this:

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

In Sheet2 I'm working with these names from Sheet1. I'm doing that in a way that I'm entering Code value in column A and in column B I get the Name, in column C I get the Last Name. That looks like this:

在 Sheet2 中，我正在使用 Sheet1 中的这些名称。我这样做的方式是在 A 列中输入代码值，在 BI 列中获取名称，在 CI 列中获取姓氏。看起来像这样：

I've done this with formulas, entering it in the formula bar. For column A(or Name) I've used this formula: =IFERROR(VLOOKUP(A2;Sheet1!A:C;2;FALSE);"")and for column B(or Last Name) I've used this one: =IFERROR(VLOOKUP(A2;Sheet1!A:C;3;FALSE);""). I've dragged these formulas to row 20 and it works great.

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

Now, what I'd like to do is to put these formulas into Excel VBA code and them to work for noted range. I've just started to use VBA and I don't know how to do it in it, tried something but doesn't work, ..., I've done this so far. I'm new to this Excel/Macro/VBA thing so any help would be appreciated.

现在，我想做的是将这些公式放入 Excel VBA 代码中，然后它们在指定范围内工作。我刚刚开始使用 VBA，我不知道如何使用它，尝试了一些但不起作用，...，到目前为止我已经这样做了。我是这个 Excel/宏/VBA 的新手，所以任何帮助将不胜感激。

采纳答案by glh

The below code will work if you type in your Codevalues in sheet2and highlight them, and run this macro:

如果您输入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:If you are wanting to update values as you type use (thank you @PeterAlbert for added optimisation!):

编辑：如果您想在键入时更新值，请使用（感谢@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 of RC:

RC的解释：

The FormulaR1C1formula types are good to use when referencing a cell with respect to the current cell. There a few rules to remember:

在FormulaR1C1相对于当前单元格引用单元格时，可以很好地使用公式类型。有一些规则要记住：

• The Rstands for Row and Cis for Column and the integer after it, if any, defines the row or column;
• As a basis the RCformula references itself;
• Any number following the Ror Cwraped in []is an offset to itself, e.g. if you are in cell A1and use R[1]C[1]you would be referencing cell B2;
• Also any number following the Rand Cis an exact, e.g. if you reference R2C2no matter the cell you are in would also point to B2; and

• TheR代表 Row 和CColumn ，后面的整数（如果有）定义行或列；
• 作为RC公式引用自身的基础；
• 下面的任何数量的R或C挤包的[]是，如果你是在细胞中的偏移本身，例如A1，用R[1]C[1]你会引用单元格B2;
• 此外，R和之后的任何数字C都是精确的，例如，如果您引用R2C2无论您所在的单元格也将指向B2; 和

To complicate things if you were in cell C5, e.g. using Range("C5").FormulaR1C1 =and coded the follwing:

如果您在 cell 中C5，则使事情复杂化，例如使用Range("C5").FormulaR1C1 =并编码以下内容：

1. "=RC[-1]"references cell B5
2. "=RC1"references cell A5, more rightly $A5
3. "=R[1]C[-2]"references cell A6
4. "=Sum(C[-1]:C5)"is =Sum(B:E), more rightly =Sum(B:$E)

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)

回答by Peter Albert

If I understand your question and comments correctly, you want to ensure that columns B&C always show you the right values based on your formula, but also want to protect (and maybe even hide the formula) from the users.

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

I'd suggest you use sheet protection instead: all you need to do is to unlock the cells you want the users to edit, i.e. select column A and in the _ Format cells_ dialog uncheck "Locked" in the Protectiontab. Similarly for columns B&C, check "Hidden". Now right click the sheet name and select Protect Sheet. Once this is done, the user can edit column A - but will not see the formula in B&C and cannot edit those cells.

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

If for some reasons you need to ensure this in VBA, use the following code:

如果出于某些原因需要在 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

You need to place this in the module of the worksheet.

您需要将其放在工作表的模块中。