VBA代码中的Excel公式 [英] Excel formula in VBA code

问题描述

所以，在Sheet1中，我有一些名字的基础，如下所示：

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

在Sheet2中，我正在使用Sheet1中的这些名称。我在这样做，我在列A中输入代码值，在列B中我得到名称，在列C中我得到姓氏。看起来像这样：

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:

我用公式做了这个，在公式栏中输入。对于列A（或名称），我使用了这个公式： = IFERROR（VLOOKUP（A2; Sheet1！A：C; 2; FALSE）;）列B（或姓氏）我使用过这个： = IFERROR（VLOOKUP（A2; Sheet1！A：C; 3; FALSE）;）。我把这些公式拖到第20行，它的效果很好。

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.

现在，我想做的是将这些公式放到Excel VBA代码中，工作注意范围。我刚刚开始使用VBA，我不知道如何做到这一点，尝试了一些但不起作用，...我已经做到了这一点。我是新的Excel / Macro / VBA的东西，所以任何帮助将不胜感激。

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.

推荐答案

下面的代码将工作if您在 sheet2 中键入代码值，并突出显示，并运行此宏：

The below code will work if you type in your Code values in sheet2 and highlight them, and run this macro:

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添加优化！）：

If you are wanting to update values as you type use (thank you @PeterAlbert for added optimisation!):

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 formula types are good to use when referencing a cell with respect to the current cell. There a few rules to remember:

• R 代表Row和 C 用于列，并且后面的整数（如果有的话）定义行或列;


• 作为基础 RC 公式引用本身;


• R 或 C之后的任何数字包含在 [] 中是对自身的偏移，例如如果您在单元格 A1 并使用 R [1] C [1] ，则将引用单元格 B2 ;


• 在 R 和 C之后的任何数字是一个确切的例子如果您引用 R2C2 ，无论您所在的单元格还将指向 B2 ;和

• The R stands for Row and C is for Column and the integer after it, if any, defines the row or column;
• As a basis the RC formula references itself;
• Any number following the R or C wraped in [] is an offset to itself, e.g. if you are in cell A1 and use R[1]C[1] you would be referencing cell B2;
• Also any number following the R and C is an exact, e.g. if you reference R2C2 no matter the cell you are in would also point to B2; and

如果您在单元格 C5 使用 Range（C5）。FormulaR1C1 = 并编码如下：

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

1. = RC [-1]参考单元格 B5


2. = RC1引用单元格 A5 ，更正确地 $ A5 / li>
   
3. = R [1] C [-2]引用单元格 A6


4. = Sum（C [-1]：C5）是 = Sum（B： E），更正确地 = Sum（B：$ E）

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)

这篇关于VBA代码中的Excel公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！