Simple vba for vlookup and replace

[Bites88]

Hi everyone,
I have a workbook with only 2 sheets - an Inventory sheet with 8-digit numbers and Item Names, and a Summary sheet with 8-digit numbers only.
We have about 2500 items. For example, Penn Tennis Balls x3 - 00899530

I would like to have some simple vba code that will allow me to run:
1) vlookup the 8-digit number on the Summary sheet with the Inventory Sheet to get the Item Name
AND
2) replace the 8-digit number on the Summary sheet with the Item Name.

Thank you!

 

[kvsrinivasamurthy]

Post sample data in XL2BB format.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook.
If it is not what you want then refer to post 2 (but with a "please" from me) and only post a small amount of representative data for each sheet and explain again in relation to that small sample data.
XL2BB details can be found here: XL2BB
And if you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

Sub Test()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Inventory")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With Sheets("Summary")
    With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      a = .Value
      For i = 1 To UBound(a)
        If d.exists(a(i, 1)) Then a(i, 1) = d(a(i, 1))
      Next i
      .Value = a
    End With
  End With
End Sub

 

[Bites88]

I'm having a bit of difficulty with the code above. Here's my attempted sample from XL2BB:

Inventory.xlsx
	F
13
Sales Summary

Inventory.xlsx
	A
6
Inventory

In case that didn't work, I have included the screenshots of the 2 sheets here:
Summary sheet

Inventory sheet

Thank you!

 

[MARK858]

You need to select the entire range you want displayed before clicking the Mini-sheet icon in XL2BB i.e. in the 2nd table (I chose the 2nd table as I can see the row numbers) you posted you only had cell A6 selected on the Inventory sheet, whereas you should have selected from cells A1 to C5 on the Inventory sheet, then clicked the mini-sheet icon.

Hopefully if you post the XL2BB tables again then @Peter_SSs will find it easier to work from (there is a testing forum in the link below so you can see how it will appear when you post)

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

 

[Peter_SSs]

From post 1 we did not know what rows/columns the data was in for either worksheet. As far as I can see now we know that, just some small changes to the code should work (provided I have also understood the requirement)

Sub Test_v2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Inventory")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With Sheets("Summary")
    With .Range("B4", .Range("B" & Rows.Count).End(xlUp))
      a = .Value
      For i = 1 To UBound(a)
        If d.exists(a(i, 1)) Then a(i, 1) = d(a(i, 1))
      Next i
      .Value = a
    End With
  End With
End Sub

Here is my Summary sheet before the code is run

Bites88.xlsm
	B
1
2
3	Item No
4	00120013
5	00120012
6	00120012
7
Summary

.. and here it is after the code has been run. Is that what you meant by this?

replace the 8-digit number on the Summary sheet with the Item Name.

Bites88.xlsm
	B
1
2
3	Item No
4	Wilson Tennis Balls
5	Penn Tennis Balls
6	Penn Tennis Balls
7
Summary

 

[Bites88]

Yes, it works! This will really help our inventory reports.
Greatly appreciate the help, and thank you everyone

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.