VBA to replace index match formula

Question

i am new to VBA and trying to find my way through the following scenario:

sheet1 with columns A (item) B (Description) C (Price) (this is my search sheet)

sheet2 with the same exact columns (this is my database sheet)

what i want to do is: When i enter an item code in sheet1 column a, match the unique value from sheet2 and copy paste the values from columns B and C to sheet1 for this item.

i want this to happen only for the active cell in sheet1, is that possible?

i hope i was clear enough kind regards

Answer 1

You said you want to use VBA to replace using a formula, but you didn't say why. VBA is great but I think formulas are the way to go for this particular job.

Format the data in Sheet1 and Sheet2 and Table1 and Table2 respectively, then:

In Table1 column B, type:

=INDEX(Table2[Description],MATCH([Item],Table2[Item],0),)

In Table1 column C, type:

=INDEX(Table2[Price],MATCH([Item],Table2[Item],0),)

Voila

Answer 2

Add following code to the Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    On Error Resume Next
    'if changed entire range ITEMS1'
    If Target.Name.Name = "ITEMS1" Then
        Dim tarRange As Range
        Set tarRange = Application.Intersect(Range("ITEMS1"), Target)
        If Not tarRange Is Nothing Then
            Dim sh2 As Worksheet
            Dim res As Range
            Dim tarRow As Range

            'change "Sheet2" to the sheet name that is true for you'
            Set sh2 = ThisWorkbook.Worksheets("Sheet2")

            For Each tarRow In tarRange.Cells

                Set res = sh2.Range("A:A").Find(What:=tarRow.Value, LookAt:=xlWhole)

                If res Is Nothing Then
                    tarRow.Offset(0, 1).Resize(1, 2).Value = "not found"
                    'MsgBox "There is no matching value on Sheet2"
                Else
                    'if we found value on sheet2, than copy range B,C'
                    tarRow.Resize(1, 3).Value = res.Resize(1, 3).Value
                End If
            Next
        End If
    End If
    Application.EnableEvents = True
End Sub