CODEWITHSUNDEEP
vbaspreadsheet
kilo43
kilo43

Reputation: 1

VBA to lookup multiple values

I have an Spreadsheet with values listed multiple times in multiple columns. Example column b thru d. Is it possible to lookup each of these values individually and return the values from Column A.

Data Table

Data Table

Output Example

Example Output

The output Sheet would already have the values to look up listed in Column A.

Upvotes: 0

Views: 461

Answers (1)

patel
patel

Reputation: 440

this is vba solution, I did not declare variables, please do it

Sub a()
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
Lastrow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To Lastrow2
  cable = sh2.Cells(r, 1)
  s = ""
  For Each cell In sh1.UsedRange
    If cell = cable Then s = s + sh1.Cells(cell.Row, 1) + ","
  Next
  sh2.Cells(r, 2) = s
Next
End Sub

Upvotes: 1

Related Questions