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

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

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