Match Code VBA

[abi_learn_vba]

Hi,

I have two column of data column B has "Branch" and column C has "Territory" as shown below in Sheet1

<table width="189" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:65pt" height="20" width="87">BRANCH </td> <td class="xl63" style="width:77pt" width="102">TERRITORY </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">300</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">300</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">300</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">301</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">204</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">404</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">303</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">303</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">303</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">6432</td> <td class="xl63">402</td> </tr> </tbody></table>
In Sheet2 Column A,B, C has value as shown below

<table width="253" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:65pt" height="20" width="87">BRANCH </td> <td class="xl65" style="width:77pt" width="102">TERRITORY </td> <td class="xl65" style="width:48pt" width="64">Code</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">300</td> <td class="xl65">A1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">300</td> <td class="xl65">A1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">300</td> <td class="xl65">A1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">301</td> <td class="xl65">C1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">204</td> <td class="xl65">D1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">404</td> <td class="xl65">E1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">303</td> <td class="xl65">K1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">303</td> <td class="xl65">K1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">303</td> <td class="xl65">K1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6432</td> <td class="xl65">402</td> <td class="xl65">Z1</td> </tr> </tbody></table>

My requirement is to check for match column B and C from sheet1 to column A and B of sheet2 and bring the corresponding "code" value to their respective column A in sheet1


I want this to be done using VBA, i tried various method but couldn't succeed. Can any one help in giving me the how should i go about with this.. what function or code can i use..

Cheers
-Abi

 

[JoeMo]

Try this:

Sub GetCode()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Dim lRw1 As Long, lRw2 As Long
Dim vA11 As Variant, vA12 As Variant, vA21 As Variant, vA22 As Variant

Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
lRw1 = sh1.Range("B" & Rows.Count).End(xlUp).Row
lRw2 = sh2.Range("A" & Rows.Count).End(xlUp).Row

Set r1 = sh1.Range("B2", "B" & lRw1)
Set r2 = sh2.Range("A2", "A" & lRw2)
vA11 = r1
vA12 = r1.Offset(0, 1)
vA21 = r2
vA22 = r2.Offset(0, 1)

For i = LBound(vA11, 1) To UBound(vA11, 1)
    For j = LBound(vA21, 1) To UBound(vA21, 1)
        If vA11(i, 1) = vA21(j, 1) And vA12(i, 1) = vA22(j, 1) Then
            r1.Cells(i).Offset(0, -1).Value = r2.Cells(j).Offset(0, 2).Value
        End If
    Next j
Next i
End Sub

 

[abi_learn_vba]

Thank you that is Great....