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]

If section A on sheet1 and section B on sheet2, maybe the following code is useful

Sub matches()
Dim na&, nb&, a, b
Dim c(), i&, j&
With Sheets("sheet2")
nb = .Cells(Rows.Count, "b").End(3).Row
b = .Cells(1, "a").Resize(nb, 2)
End With
With Sheets("sheet1")
na = .Cells(Rows.Count, "a").End(3).Row
ReDim c(1 To na, 1 To 1)
a = .Cells(1, "a").Resize(na)
For i = 1 To na
    For j = 1 To nb
        If InStr(a(i, 1), b(j, 1)) > 0 Then
            c(i, 1) = b(j, 2)
            Exit For
        End If
Next j, i
.Cells(1, "b").Resize(na) = c
End With
End Sub

 

[kashyap]

This code is working good only for the example but when I try this on actual sheet, I am not getting any results.. Any idea why?

 

[mirabeau]

Are your sheets named sheet1 and sheet2, so that the VBA coding Sheets("sheet1") etc does in fact access the correct sheets?

Are the data listed in Columns A and Columns B on each sheet (as they are in the example)?

Are you sure there should be a result? That is, have you confirmed manually that some of the relevant strings do in fact match up, as they do in the example.

 

[kashyap]

Got it.. it is case sensitive..

1. How to make this not to be case sensitive?
2. How to change the columns of section B in sheet2?

 

[mirabeau]

Got it.. it is case sensitive..

1. How to make this not to be case sensitive?
2. How to change the columns of section B in sheet2?

I have to go off line and can't do any more for some time.

But I guess your capable hands should sort it out OK.

Good luck!

 

[jim may]

What is the significance of the 3 in End(3) below?


nb = .Cells(Rows.Count, "b").End(3).Row

 

[mirabeau]

Got it.. it is case sensitive..

1. How to make this not to be case sensitive?
2. How to change the columns of section B in sheet2?

RE 1.
You could try modifying the part of the code as indicated below and see if this makes it case insensitive enough for you

.....
For i = 1 To na
    For j = 1 To nb
        If InStr(LCase(a(i, 1)), LCase(b(j, 1))) > 0 Then
            c(i, 1) = b(j, 2)
            Exit For
        End If
Next j, i
.....

RE 2.
You could type in any changes you like, or copy changes from elsewhere.
I guess you probably don't mean this though, but I don't know what you are looking for without some further detail.

 

[mirabeau]

What is the significance of the 3 in End(3) below?


nb = .Cells(Rows.Count, "b").End(3).Row

hi Jim,

If you're asking what does it mean in the VBA then it's just End(3)=End(xlUp)

If you're asking why I write End(xlUp) it that way, I suppose for several reasons like:

It makes no difference to the computer processing the code. Or maybe some computers read numbers a bit faster/better than they do text.

The viewpoint stemming from the medieval scholastic writer William of Ockham that "entities should not be multiplied beyond necessity" or "it is vain to do with more what can be done with fewer", which viewpoint has a long and interesting history.

It's quicker/easier for me to type the code. I don't like typing very much.

I think for anyone learning VBA coding it should be as easy to learn what End(3) means as what End(xlUp) means.

I just like concise rather than prolix codes provided they do the job as well.

I think that most end users are probably concerned with whether the code works as they want rather than with whether they can understand every line. I suspect that on a forum like this, most of us don't understand every line of each other's code except in fairly simple cases or when we take the trouble and time to do so, which I suspect isn't very often.

Probably could go on a bit more, but I hope your question is adequately answered.

 

[tropix100]

Jim,

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

Lets not waste typing on replies