VBA: Search column for value then return a cooresponding value

[excelnubie95]

I am working on a macro that searches through Column A (Sheet 1) for a list of values on Sheet 2 Column A. Once a value is found in Column A (Sheet 1) from Sheet 2 Column A, the macro returns the corresponding value in Sheet 1 column B from Sheet 2 Column B. The number that is being searched in Sheet 1 Column A, can be anywhere in the cell. I am using Excel 2013.

Sheet 1
A
78963-1234
79432-4321
0987-56834
4321-84356
.
.
.
Sheet 2
A | B
1234 | product 1
4321 | product 2
0987 | product 3
.
.
.
After Macro is run Sheet 1 would look like this:
Sheet 1
A | B
78963-1234 | product 1
79432-4321 | product 2
0987-56834 | product 3
4321-84356 | product 2

 

[excelnubie95]

Below is the code I have so far:

Sub Update_Part_ID()

'Start timer
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

Dim Sheet1 As Worksheet, Sheet2 As Worksheet 'sheet = Sheet1, vintage = Sheet2
Dim d As Range, c As Range
Dim lastrow As Long, sr As Long, n As Long, i As Long
Set Sheet1 = Sheets("Sheet1")
Set Sheet2 = Sheets("Sheet2")
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row 'lr = Lastrow

With Sheet1
For Each d In Sheet2.Range("A2:A" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row)
sr = 1
n = Application.CountIf(Sheet1.Columns(1), d)
If n = 1 Then
Set c = Sheet1.Range("A" & sr & ":A" & lastrow).Find(d.Value, LookAt:=xlWhole)
If Not c Is Nothing Then
Sheet1.Range("B" & c.Row) = Sheet2.Range("B" & d.Row)
End If
ElseIf n > 1 Then
For i = 1 To n
Set c = Sheet1.Range("A" & sr & ":A" & lastrow).Find(d.Value, LookAt:=xlWhole)
If Not c Is Nothing Then
Sheet1.Range("B" & c.Row) = Sheet2.Range("B" & d.Row)
sr = c.Row
End If
Next i
End If
Next d
End With
Sheet1.Activate

'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

 

[Takae]

All number in columnA of Sheet2 must be string.
I divided a number with "-". Is this o.k.?

Sub Update_Part_IDe()
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim buf1 As String, buf2 As String, tmp1, tmp2
Dim i As Long, LastR As Long, cnt As Long
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
tmp1 = ""
tmp2 = ""
With ws1
    LastR = .Cells(Rows.count, 1).End(xlUp).row
    For i = 2 To LastR
        cnt = InStr(.Cells(i, 1), "-")
        buf1 = Left(.Cells(i, 1).Value, cnt - 1)
        buf2 = Mid(.Cells(i, 1).Value, cnt + 1, 100)
            On Error Resume Next
            tmp1 = WorksheetFunction.VLookup(buf1, ws2.Range("A:B"), 2, 0)
            tmp2 = WorksheetFunction.VLookup(buf2, ws2.Range("A:B"), 2, 0)
            If tmp1 <> "" Then
                .Cells(i, 2).Value = tmp1
            Else
                If tmp2 <> "" Then
                    .Cells(i, 2).Value = tmp2
                End If
            End If
            On Error GoTo 0
            tmp1 = ""
            tmp2 = ""
    Next
End With
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

 

[excelnubie95]

Hi Takae,

I tried the code you posted and it did not work for me. The values were not copied over.

 

[Takae]

Is "1234" in sheet2 string?
Numeric and string are mixed in columnA of sheet2?

 

[excelnubie95]

Sheet 1 can have letters and numbers. The value that is searched from Sheet 2, will always be a numeric.

 

[Takae]

The "0987" in sheet2. It must be string. So I think solumnA of sheet2 is all string or mixed with numeric.
But it will be O.K, because it can be changed in a macro.

The problem is data of sheet1. It has spaces but not all data?
I have copied your sample data but I was not able to remove these spaces.
If all data has a space, it is O.k, but some data has a space, I am not able to write a code.