replace item for range with item for Table based on column

[abdo meghari]

Hello
here is data in sheet1 as TABLE not range.

TABLE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	1	T01 185/65 R15	188
3	2	1200R20 G580 JAP	102
4	3	1200R20 R187 JAP	133
5	4	1200R24 G580 JZ	0
6	5	1300R22.5 R187 JAP	147
7	6	1400R20 R180 JAP	97
8	7	175/65R14 B25A32 THI	150
9	8	175/70R13 EP150 THI	4
10	9	175/70R14 150 E2 THI	30
11	10	185/65R14 EP150 INDO	16
12	11	185/65R15 B250 JAP	187
13	12	185/70R13 EP 150 INDO	9
14	13	195/55R16 EP300 THI	195
15	14	195/60R16 150EZ THI	140
sheet1

and here is data in sheet2 as in range , not Table

TABLE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	5	BS 13R22.5 R187 JAP	34
3	6	BS 1400R20 R180 JAP	55
4	3	BS 1200R20 R187 JAP	32
5	4	BS 1200R24 G580 JAP	12
6	7	BS 175/65R14 B25A32 THI	23
7	1	BS 185/65R15 T001 JAP	45
8	2	BS 1200R20 G580 JAP	67
9	8	BS 175/70R13 EP150 THI	67
10	9	BS 175/70R14 ZE150 THI	88
11	12	BS 185/70R13 EP150 INDO	99
12	13	BS 195/55R16 EP300 THI	80
13	14	FS 195/60R16 150EZ THI	68
14	10	BS 185/65R14 EP150 INDO	16
15	11	BS 185/65R15 B250 JAP	187
sheet2

so I want replace items in sheet2 with item in sheet1 based on column A
the result in sheet1 should be

TABLE.xlsm
	A	B	C
1	ITEM	BRAND	QTY
2	1	BS 185/65R15 T001 JAP	188
3	2	BS 1200R20 G580 JAP	102
4	3	BS 1200R20 R187 JAP	133
5	4	BS 1200R24 G580 JAP	0
6	5	BS 13R22.5 R187 JAP	147
7	6	BS 1400R20 R180 JAP	97
8	7	BS 175/65R14 B25A32 THI	150
9	8	BS 175/70R13 EP150 THI	4
10	9	BS 175/70R14 ZE150 THI	30
11	10	BS 185/65R14 EP150 INDO	16
12	11	BS 185/65R15 B250 JAP	187
13	12	185/70R13 EP 150 INDO	9
14	13	BS 195/55R16 EP300 THI	195
15	14	FS 195/60R16 150EZ THI	140
sheet1

and update in sheet1 automatically when add new or change the items when every time run the macro

 

[Legacy 143009]

Sheet2 C2:

=VLOOKUP(A2,Table1,3,0)

EDIT: Sorry, I got it wrong.

 

[abdo meghari]

Hi
I search for macro as I said

and update in sheet1 automatically when add new or change the items when every time run the macro

 

[Legacy 143009]

You must fix the table name:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 And Not Intersect(Target, ListObjects("Table1").Range) Is Nothing Then
    Dim r As Boolean
    r = True
    Application.EnableEvents = False
    With ListObjects("Table1").Range
    For Each val2 In .Rows(Target.Row).Value2
      If val2 = "" Then
        r = False
      End If
    Next
    If r Then
      For i = .Cells(1, 1).Row + 1 To .Rows(.Rows.Count).Row
        For j = 2 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
          If .Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
            .Cells(i, 2).Value = Worksheets("Sheet2").Cells(j, 2).Value
          End If
        Next
      Next
    End If
    Application.EnableEvents = True
    End With
  End If
End Sub

 

[Legacy 143009]

Or:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 And Not Intersect(Target, ListObjects("Table1").Range) Is Nothing Then
    Application.EnableEvents = False
    With ListObjects("Table1").Range
    If Not IsEmpty(.Cells(Target.Row - (.Cells(1, 1).Row - 1), 1).Value2) Then
      For i = .Cells(1, 1).Row + 1 To .Rows(.Rows.Count).Row
        For j = 2 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
          If .Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
            .Cells(i, 2).Value = Worksheets("Sheet2").Cells(j, 2).Value
            Exit For
          End If
        Next
      Next
    End If
    Application.EnableEvents = True
    End With
  End If
End Sub

 

[Legacy 143009]

Or get rid of check column A completely:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 And Not Intersect(Target, ListObjects("Table1").Range) Is Nothing Then
    Application.EnableEvents = False
    With ListObjects("Table1").Range
      For i = 2 To .Rows(.Rows.Count).Row
        For j = 2 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
          If .Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
            .Cells(i, 2).Value = Worksheets("Sheet2").Cells(j, 2).Value
            Exit For
          End If
        Next
      Next
    End With
    Application.EnableEvents = True
  End If
End Sub

 

[abdo meghari]

your last version is awesome !
can you make the macro in standard module instead of sheet module please?

 

[Legacy 143009]

In standard module:

Sub vLookup(ByVal Trgt As Range)
  If Trgt.Count = 1 And Not Intersect(Trgt, Worksheets("Sheet1").ListObjects("Table1").Range) Is Nothing Then
    Application.EnableEvents = False
    With Worksheets("Sheet1").ListObjects("Table1").Range
      For i = 2 To .Rows(.Rows.Count).Row
        For j = 2 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
          If .Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
            .Cells(i, 2).Value = Worksheets("Sheet2").Cells(j, 2).Value
            Exit For
          End If
        Next
      Next
    End With
    Application.EnableEvents = True
  End If
End Sub

To call it on worksheet cahge:

Private Sub Worksheet_Change(ByVal Target As Range)
  Call vLookup(Target)
End Sub

 

[Legacy 143009]

If you want only the change part then:

    With Worksheets("Sheet1").ListObjects("Table1").Range
      For i = 2 To .Rows(.Rows.Count).Row
        For j = 2 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
          If .Cells(i, 1).Value = Worksheets("Sheet2").Cells(j, 1).Value Then
            .Cells(i, 2).Value = Worksheets("Sheet2").Cells(j, 2).Value
            Exit For
          End If
        Next
      Next
    End With

Use this piece at whereever you want.

 

[abdo meghari]

thanks and sorry I'm not clear !
I mean by press the button .

If you want only the change part then:

I'm not sure what you mean !