Macro to search string

K

kashyap

Board Regular
Joined
Mar 28, 2009
Messages
173
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
If section A on sheet1 and section B on sheet2, maybe the following code is useful
Code: 
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
 
Upvote 0
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?:confused:
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
kashyap said:
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?
Click to expand...
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!
 
Upvote 0
What is the significance of the 3 in End(3) below?


nb = .Cells(Rows.Count, "b").End(3).Row
 
Upvote 0
kashyap said:
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?
Click to expand...

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
Rich (BB code): 
.....
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.
 
Upvote 0
jim may said:
What is the significance of the 3 in End(3) below?


nb = .Cells(Rows.Count, "b").End(3).Row
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
574
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
538
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
913
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,654
Latest member
mememe101

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top