Update Data Automatically

[Ivy_1011]

I have an excel worksheet that when I run the code, it enters the cell data based on the number I place in Column 1 or item column. In order to retrieve the data, I have to run the code using a macro-assigned button.

My objective is to have the data populated in Columns 2 and 3 automatically upon entering the item number in column 1. Below is the code and tables per Tab name. I tried uploading the workbook but I do not see that option anymore.

Sub Input_Data()

Dim i As Integer

On Error Resume Next

For i = 2 To 30
Worksheets("Test").Cells(i, 2) = Application.WorksheetFunction.VLookup(Worksheets("Test").Cells(i, 1), Worksheets("Imports").Range("A2:C30"), 2)
Worksheets("Test").Cells(i, 3) = Application.WorksheetFunction.VLookup(Worksheets("Test").Cells(i, 1), Worksheets("Imports").Range("A2:C30"), 3)
Next i


End Sub

"Test" Tab

Item	Description	Color
1	peach	coral
1	peach	coral
2	apple	red
1	peach	coral
5	kiwi	green
1	peach	coral
1	peach	coral

"Imports" Tab

1​	peach	coral
2​	apple	red
3​	orange	orange
4​	kiwi	green

 

[Joe4]

Go to the Test sheet, and right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if update made to column 1 below row 1
    If Target.Column = 1 And Target.Row > 1 Then
'       Populate formulas in columns B and C
        Application.EnableEvents = False
        Target.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(Target.Value, Worksheets("Imports").Range("A2:C30"), 2)
        Target.Offset(0, 2).Value = Application.WorksheetFunction.VLookup(Target.Value, Worksheets("Imports").Range("A2:C30"), 3)
        Application.EnableEvents = True
    End If
   
End Sub

This should automatically update columns B and C whenever you manually type an entry into column A on that sheet.

 

[Ivy_1011]

Yes, adding the code works perfectly. Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!