Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro with strings
Hello?
I have two columns of sentnces. Column A has about 5000 sentences Column C has about 30 short sentences or words. I would like the macro to find sentences or words that are present in column C , in column A. If the word or sentence from the column C is found in the Column A, it should be copied from C to Column B. example: columnA ColumnC many new books new boo so to columnB should be copied string (new boo) because it is also present in column A. Thank you very much in advance for help. Tomek -- Tomek |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro with strings
This code assumes that your data in column C is continuious with no empty
rows (it stops at the first empty row). It processes the active sheet but that can be changed easily, and assumes that the data starts in the second row... Public Sub FindDuplicates() Dim shtCurrent As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim strFirstAddress As String Dim rngToFind As Range Set shtCurrent = ActiveSheet Set rngToFind = shtCurrent.Range("C2") Set rngToSearch = shtCurrent.Range("A1").EntireColumn Do While rngToFind < Empty Set rngFound = rngToSearch.Find(rngToFind.Value, , , xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound.Offset(0, 1).Value = rngToFind Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If Set rngToFind = rngToFind.Offset(1, 0) Loop End Sub HTH "Tomek" wrote: Hello? I have two columns of sentnces. Column A has about 5000 sentences Column C has about 30 short sentences or words. I would like the macro to find sentences or words that are present in column C , in column A. If the word or sentence from the column C is found in the Column A, it should be copied from C to Column B. example: columnA ColumnC many new books new boo so to columnB should be copied string (new boo) because it is also present in column A. Thank you very much in advance for help. Tomek -- Tomek |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro with strings
Thank you very much, that's what I needed and I couldn't do it myself.
Tomek Jim Thomlinson wrote: This code assumes that your data in column C is continuious with no empty rows (it stops at the first empty row). It processes the active sheet but that can be changed easily, and assumes that the data starts in the second row... Public Sub FindDuplicates() Dim shtCurrent As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim strFirstAddress As String Dim rngToFind As Range Set shtCurrent = ActiveSheet Set rngToFind = shtCurrent.Range("C2") Set rngToSearch = shtCurrent.Range("A1").EntireColumn Do While rngToFind < Empty Set rngFound = rngToSearch.Find(rngToFind.Value, , , xlPart) If Not rngFound Is Nothing Then strFirstAddress = rngFound.Address Do rngFound.Offset(0, 1).Value = rngToFind Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress End If Set rngToFind = rngToFind.Offset(1, 0) Loop End Sub HTH "Tomek" wrote: -- Tomek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
VBA macro to join up strings | Excel Programming | |||
VBA macro to join up strings | Excel Programming | |||
Excel Macro Strings | Excel Programming |