Compare 2 columns in different sheets and return value of third column in second sheet

[johnjohnk]

Hi all,

I am trying to do the following:

In the same workbook, I have names in column B in sheet 1 and names in column B in sheet 2. There are names in sheet 2 that are not in sheet 1.
I would like a macro that for every name in sheet 2 will find the same name in sheet 1 and when found will copy the data from column F of the same row of sheet 1 in column C of sheet 2.
If the name in sheet 2 is not found in sheet 1, then report 0.
All the names in sheet 1 and sheet 2 are in the exact same format (IE_Last Name_First Name).

I am enclosing an example.

I tried to find a solutions on the forums but couldn't find any that suit my needs. Of course I know I could use the match index formula but I really need this in VBA.

Thanks in advance for your help!

[PCI]

See next code

Option Explicit

Sub Treat()
Const OrgWsName As String = "Sheet1"
Const DstWsName As String = "Sheet2"
Const OrgCol As String = "F"
Const DstCol As String = "C"
Const WkCol As String = "B"
Const FR As Integer = 2
Dim Rg As Range
Dim DataDic   As Object
Set DataDic = CreateObject("Scripting.Dictionary")

    With Sheets(OrgWsName)
        For Each Rg In Range(.Cells(FR, WkCol), .Cells(Rows.Count, WkCol).End(3))
            DataDic.Item(Rg.Value) = .Cells(Rg.Row, OrgCol).Value
        Next Rg
    End With
    With Sheets(DstWsName)
        For Each Rg In Range(.Cells(FR, WkCol), .Cells(Rows.Count, WkCol).End(3))
            .Cells(Rg.Row, DstCol) = 0
            If (DataDic.exists(Rg.Value)) Then _
                .Cells(Rg.Row, DstCol) = DataDic.Item(Rg.Value)
        Next Rg
    End With
    MsgBox (" Job Done")
End Sub

[johnjohnk]

Wow it's perfectly working!!! Thank you so much for your quick response!!

[PCI]

You are welcome, not too difficult and good explanation with sample ...!
Keep in mind for the Rep ...!