autofill column for each item based on matching column between two sheets

[abdo meghari]

Hello
I want macro to matching column B between two sheets and fill missed codes for column A in REPORT sheet.

as
	A	B
1	CODE	BRAND
2	BSJ100	BS 1200R20 G580 JAP
3	BSJ100	BS 1200R20 G580 JAP
4	BSJ100	BS 1200R20 G580 JAP
5	BSJ100	BS 1200R20 G580 JAP
6	BSJ100	BS 1200R20 G580 JAP
7	BSJ101	BS 1200R20 G580 THI
8	BSJ101	BS 1200R20 G580 THI
9	BSJ101	BS 1200R20 G580 THI
10	BSJ102	BS 1200R24 G582 JAP
11	BSJ102	BS 1200R24 G582 JAP
12	BSJ102	BS 1200R24 G582 JAP
13	BSJ102	BS 1200R24 G582 JAP
14	BSJ103	BS 1200R20 R187 JAP
15	BSJ103	BS 1200R20 R187 JAP
16	BSJ104	BS 1200R20 R187 THI
17	BSJ105	BS 1200R24 G580 JAP
18	BSJ105	BS 1200R24 G580 JAP
19	BSJ106	BS 13R22.5 R187 JAP
20	BSJ107	BS 1400R20 R180 JAP
21	BSJ107	BS 1400R20 R180 JAP
22	BSJ108	BS 1400R20 R180BZ JAP
23	BSJ109	BS 1400R20 VSJ JAP
24	BSJ109	BS 1400R20 VSJ JAP
25	BSJ109	BS 1400R20 VSJ JAP
26	BSJ110	BS 155R12C R624 INDO
27	BSJ111	BS 155R12C R623 INDO
MAIN

as
	A	B
1	CODE	BRAND
2		BS 1200R24 G580 JAP
3		BS 13R22.5 R187 JAP
4		BS 1400R20 R180 JAP
5		BS 1400R20 R180BZ JAP
6		BS 1400R20 VSJ JAP
7		BS 1200R20 G580 JAP
8		BS 1200R20 G580 THI
9		BS 1200R20 R187 JAP
10		BS 1200R20 R187 THI
REPORT

result

as
	A	B
2	BSJ105	BS 1200R24 G580 JAP
3	BSJ106	BS 13R22.5 R187 JAP
4	BSJ107	BS 1400R20 R180 JAP
5	BSJ108	BS 1400R20 R180BZ JAP
6	BSJ109	BS 1400R20 VSJ JAP
7	BSJ100	BS 1200R20 G580 JAP
8	BSJ101	BS 1200R20 G580 THI
9	BSJ103	BS 1200R20 R187 JAP
10	BSJ104	BS 1200R20 R187 THI
REPORT

thanks

 

[Legacy 143009]

Are all codes empty in REPORT sheet or do you have some of them?

 

[abdo meghari]

yes all codes empty in REPORT sheet .

 

[Legacy 143009]

I haven't tested but should work:

Sub test()
  Dim MAIN As Worksheet, REPORT As Worksheet, i As Long
  Dim MAINRange As Range, REPORTRange As Range

  Set MAIN = Worksheets("MAIN")
  Set MAINRange = Intersect(MAIN.UsedRange, MAIN.Range("A:B"))
  Set REPORT = Worksheets("REPORT")
  Set REPORTRange = Intersect(REPORT.UsedRange, REPORT.Range("A:B"))

  With REPORTRange
  For i = 2 To .Rows.Count
    .Cells(i, 1).Value = WorksheetFunction.Lookup(.Cells(i, 2).Value, MAINRange.Columns(2), MAINRange.Columns(1))
  Next
  End With
End Sub

 

[abdo meghari]

perfect !
can you modify code ,please?
in sheet REPORT the BRAND column will be C ,not to B , then will match column B for MAIN sheet with column C for REPORT sheet and fill codes in column A in REPORT sheet, sorry about this error! .

 

[Legacy 143009]

Like this?

Sub test()
  Dim MAIN As Worksheet, REPORT As Worksheet, i As Long
  Dim MAINRange As Range, REPORTRange As Range

  Set MAIN = Worksheets("MAIN")
  Set MAINRange = Intersect(MAIN.UsedRange, MAIN.Range("A:B"))
  Set REPORT = Worksheets("REPORT")
  Set REPORTRange = Intersect(REPORT.UsedRange, REPORT.Range("A:C"))

  With REPORTRange
  For i = 2 To .Rows.Count
    .Cells(i, 1).Value = WorksheetFunction.Lookup(.Cells(i, 3).Value, MAINRange.Columns(2), MAINRange.Columns(1))
  Next
  End With
End Sub

 

[abdo meghari]

yes exactly this is what I want it .
again many thanks for your solutions to my threads .

 

[Legacy 143009]

No problem. Your project is simple and fun to work