VBA code to look up a list of values based on entered values and return all values.

[dnwadams]

Hi All,

I have a problem which i am hoping someone can help me solve using VBA, I have a table that looks like this:

Group No. (Key) Material
9400028741 600-1014:TELSPEC
9400028741 597/29:TELSPEC
9400030162 BFD328507:ERICSSON
9400030162 BFD328507/11:ERICSSON
9400030162 BMS 404 030/2:EMERSONRGY
9400030162 INV-1000VA RM:CODECOM
11GHZODU GE9234:SIAE
11GHZODU GE9348:SIAE
12647I Q9662A:NEC
12647I Q9662B:NEC
12647I Q9668A:NEC
12647I Q9668B:NEC

What i need is to be able to enter a list of materials (from different groups) and then have an output of all the materials that belong to the group for which the materials i entered to begin with belong to.

So if i entered the following materials in A1-A3:
BFD328507:ERICSSON
Q9668B:NEC
GE9234:SIAE

Then the output would be:
BFD328507:ERICSSON
BFD328507/11:ERICSSON
BMS 404 030/2:EMERSONRGY
INV-1000VA RM:CODECOM
Q9668B:NEC
Q9668A:NEC
Q9662B:NEC
Q9662A:NEC
GE9234:SIAE
GE9348:SIAE

Hopefully this makes sense, thanks in advance!

[Kaper]

So in A1:Axx there are search items.
Let's assume that reference table is in C:D columns and output shall go to B column.
Try the (easy to read but highly not-optimized) code:

Sub test()
Dim outrow As Long, i As Long, j As Long, ID As Variant, referencerow As Long
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  referencerow = WorksheetFunction.Match(Cells(i, "A"), Columns("D"), 0)
  ID = Cells(referencerow, "C")
  For j = 1 To Cells(Rows.Count, "C").End(xlUp).Row
    If Cells(j, "C").Value = ID Then
      outrow = outrow + 1
      Cells(outrow, "B").Value = Cells(j, "D").Value
    End If
  Next j
Next i
End Sub

[dnwadams]

Hi Kaper,

This worked great! Thanks so much for your help!