Macro to search string

[kashyap]

I am trying to get a macro for below.

In the below example, section 1 is the column where the code needs to check for particular string as per Column A in 2nd section and if the string matches it should give me the corresponding contents of Column B.

Section A and Section B are in different sheets.

<table width="146" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:62pt" width="82" height="20">Section A</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Col A</td> <td>Col B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">bpc01ctsx7</td> <td class="xl65" style="border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">htd000rsaxi</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">qap010rbb27</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">htd0rsaxi</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">qap100natr</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">qa010rbb27</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">qap02aingx7</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">qap100natr7</td> <td class="xl65" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Section B</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Col A</td> <td>Col B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">cts</td> <td class="xl65" style="border-left:none">City</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">sax</td> <td class="xl65" style="border-top:none;border-left:none">State</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">rbb</td> <td class="xl65" style="border-top:none;border-left:none">Rural</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">ing</td> <td class="xl65" style="border-top:none;border-left:none">Include</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">atr</td> <td class="xl65" style="border-top:none;border-left:none">Atmost</td> </tr> </tbody></table>
Can anyone help me with this pls.. Thank you in advance.

 

[mirabeau]

Jim,

1 = xlLeft
2 = xlRight
3 = xlUp
4 = xlDown

Lets not waste typing on replies

Maybe it's sometimes worth wasting a bit of time typing.

1 = xltoLeft
2 = xltoRight
3 = xlUp
4 = xlDown

Maybe it all helps someone.

 

[jim may]

Thanks guys.....

Jim

 

[markmzz]

Kashyap,

Try this formula:

=LOOKUP(INDEX($A$17:$A$21,SUMPRODUCT(--NOT(ISERROR(FIND($A$17:$A$21,A4))),{1,2,3,4,5})),$A$17:$B$21,2,FALSE)

Markmzz

 

[markmzz]

Kashyap,

Try this new formula:

=LOOKUP(INDEX($A$17:$A$21,SUMPRODUCT(--NOT(ISERROR(FIND($A$17:$A$21,A4))),ROW($A$17:$A$21)-ROW($A$17)+1)),$A$17:$B$21,2,FALSE)

Markmzz

 

[kashyap]

Thank you..

 

[markmzz]

Thank you..

Kashyap,

Thanks for the feedback.

Markmzz