Excel formula in VBA code

Question

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

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:

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.

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.

Answer 1

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

Edit: 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

Explinatioin of 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:

• 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

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

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)

Answer 2

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.

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 Protection tab. 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.

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

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.