Find value in string and return reference cell

[lbdgolfer]

I have been a long time viewer of this forum but just recently signed up. Based on what I have found on here I know that someone has an answer to this problem.
I have a cell in column B that contains a string of values (example 1) on one sheet in a workbook. On another sheet I have cells that contain the single values found in column B. I am trying to return the value in Column A when a match if found in Column B. I can use the Find function to determine starting position but I haven't found a way to find the row reference after finding the value in column B. Assume that the value in column B only occurs once across all rows. 'Value 1' will only be in one cell in one row.

Match value
Value 1
Value 2
Value 3
etc...


Example 1

A	B
Ref1	Value 1, Value 2, Value 3, Value 10
Ref2	Value 4, Value 5, Value 9, Value 15

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[mumps]

This macro will return the value from column A to column B in the first sheet. Change the sheet names to suit your needs.

Sub FindVal()
    Application.ScreenUpdating = False
    Dim Val As Range
    Dim foundVal As Range
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each Val In Sheets("Sheet1").Range("A2:A" & LastRow)
        Set foundVal = Sheets("Sheet2").Columns("B").Find(Val, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            Val.Offset(0, 1) = foundVal.Offset(0, -1)
        End If
    Next Val
    Application.ScreenUpdating = True
End Sub

 

[lbdgolfer]

Thanks for the quick reply. I'm not the best at macros but was able to figure out the changes necessary to match my current sheets.

 

[mumps]

Glad to help.