 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007Macro needed to find text
I have sheet with 2 columns and 1200 rows. Column A is numbers 1 to 1200 Column B is varying length text strings. I wish to paste different longer text strings into column C and then find if any of the strings in column B occur within the strings in column C. If yes, I wish to have the corresponding number from column A put into column D. e.g. 1 abc defghij 2 2 efgh xyzxyz 3 mnopq wwwabcwww 1 4 rstu wefghw 2 Is this possible with a function or a macro? Regards Have a look in the vba help index for FINDNEXT  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I have sheet with 2 columns and 1200 rows. > Column A is numbers 1 to 1200 [quoted text clipped - 15 lines] > > Regards > Have a look in the vba help index for FINDNEXT > [quoted text clipped - 22 lines] > > > > Regards I have never user VBA Regards Give some detailed examples of what you would put in col C and what you expect in col D SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> >> Have a look in the vba help index for FINDNEXT [quoted text clipped - 27 lines] > > Regards > Give some detailed examples of what you would put in col C and what you > expect in col D [quoted text clipped - 34 lines] > > > > Regards I wish to find any string from col B occurring in any of the longer strings in col C, and if found require the index no. from col A [or if easier, the row no.] to be inserted in col D. Does this make it clearer? Col A B C D Row 1 1 abc xxefghxxx 2 2 2 efgh xxyyyxx 3 3 mnopq xxxabcxxx 1 4 4 rstu xxxefghxxx 2 5 5 vwxy xxrstuxxx 4 Regards Either of these will do. Just change the references to suit. Sub ifinstr() For Each x In Range("h1:h5") For Each c In Range("i1:i5") If InStr(1, c, x) > 0 Then c.Offset(, 1) = x.Row Next c Next x End Sub Sub iffound() For Each cel In Range("h1:h5") With Columns("i") Set c = .Find(cel, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do c.Offset(, 1) = cel.Row Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With Next cel End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> >> Give some detailed examples of what you would put in col C and what you [quoted text clipped - 54 lines] > > Regards > Either of these will do. Just change the references to suit. > Sub ifinstr() [quoted text clipped - 83 lines] > > > > Regards Don Thank you The first routine works well. However I have noticed a small problem. Some of my larger strings in column C contain more than one string from column B. How can I have these identified , perhaps in further columns E,F,G,H etc Col A B C D E F Row 1 1 abc xxefghxxx 2 2 2 efgh xxyyyxx 3 3 mnopq xxxabcxxrstuxvwxyxx 1 4 5 4 4 rstu xxxefghxxx 2 5 5 vwxy xxrstuxxxmnopqxx 4 3 Regards This seems to do it Sub iffound() For Each cel In Range("h1:h5") With Columns("i") Set c = .Find(cel, lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do mc = Cells(c.Row, cel.Column).End(xlToRight).Column + 1 Cells(c.Row, mc) = cel.Row Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With Next cel End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> >> Either of these will do. Just change the references to suit. [quoted text clipped - 108 lines] > > Regards > This seems to do it > Sub iffound() [quoted text clipped - 130 lines] > > > > Regards Don The second routine does not seem to do anything. I am using Visual Basic 6.0 Regards This is vba within excel and was fully tested. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >> >> This seems to do it [quoted text clipped - 142 lines] > > Regards Send a workbook to me if desired. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > This is vba within excel and was fully tested. > [quoted text clipped - 146 lines] >> >> Regards > Send a workbook to me if desired. > [quoted text clipped - 159 lines] > >> > >> Regards Don I think I have found the problem. It may have been a variables issue. I was running routine 2 after running routine 1. I opened a new workbook and it ran ok. I am familiar with BASICA and QBASIC but I am just getting started with VBA. Can you recommend a good tutorial? > I think I have found the problem. It may have been a variables issue. I > was running routine 2 after running routine 1. > > I opened a new workbook and it ran ok. That routine works ok but the result does not fully meet my needs. I need to take another approach. I wish to search a block of text in a cell for any strings of text within double quotation marks and put this string in a new column. If there are more than one set of quotation marks then the subsequent strings should be put in the next row. Is this possible? e.g.. xxx"abc"xxxx"def"xxxx"ghijk"xxxxxx abc def ghijk Regards > Either of these will do. Just change the references to suit. > Sub ifinstr() [quoted text clipped - 83 lines] > > > > Regards Don The first routine is working well. However The number of rows in both columns varies and I have to change the macro each time. Can it be modified to check down both columns for all strings in that column. Regards Maybe you could just use a formula in column C. I put your data in A1:B4 and this array formula in C1 =SUM(--ISNUMBER(SEARCH($A$1:$A$4,B1))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. Then dragged down to C4. > I have sheet with 2 columns and 1200 rows. > Column A is numbers 1 to 1200 [quoted text clipped - 15 lines] > > Regards SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.