Hello,

I have a problem to write a macro as below:
I have one column with long text and there are several thousands rows.
I have also a second column with about fifty rows with different words or
short three- words sentences in it.
I would like to have a macro that searches the first column for the same
words or short sentence as in the second column. If the word or the short
sentence is found in the first column , it is copied and pasted to the
third column.

Thank you very much in advance for help.

Tomek

Assume the first column is A1:A5000
second coumn is B1:B50

Sub CheckWords()
dim cell as Range, rw as Long
rw = 1
for each cell in Range("B1:B50")
if application.countif(A1:A5000,"*" & cell.Value & "*") 0 then
cells(rw,3).Value = cell.Value
rw = rw + 1
end if
Next
End Sub



--
Regards,
Tom Ogilvy


"Tomek" wrote in message
...
Hello,

I have a problem to write a macro as below:
I have one column with long text and there are several thousands rows.
I have also a second column with about fifty rows with different words or
short three- words sentences in it.
I would like to have a macro that searches the first column for the same
words or short sentence as in the second column. If the word or the short
sentence is found in the first column , it is copied and pasted to the
third column.

Thank you very much in advance for help.

Tomek

Dnia Mon, 31 Jan 2005 15:21:01 -0500, Tom Ogilvy napisa³(a):

Assume the first column is A1:A5000
second coumn is B1:B50

Sub CheckWords()
dim cell as Range, rw as Long
rw = 1
for each cell in Range("B1:B50")
if application.countif(A1:A5000,"*" & cell.Value & "*") 0 then
cells(rw,3).Value = cell.Value
rw = rw + 1
end if
Next
End Sub

Something is wrong because when I try it I get a news " mismatch type"

Tomek

typo/omission

if application.countif(A1:A5000,"*" & cell.Value & "*") 0 then
should be

if application.countif(Range("A1:A5000"), _
"*" & cell.Value & "*") 0 then

--
Regards,
Tom Ogilvy

"Tomek" wrote in message
...
Dnia Mon, 31 Jan 2005 15:21:01 -0500, Tom Ogilvy napisa³(a):

Assume the first column is A1:A5000
second coumn is B1:B50

Sub CheckWords()
dim cell as Range, rw as Long
rw = 1
for each cell in Range("B1:B50")
if application.countif(A1:A5000,"*" & cell.Value & "*") 0 then
cells(rw,3).Value = cell.Value
rw = rw + 1
end if
Next
End Sub

Something is wrong because when I try it I get a news " mismatch type"

Tomek

Dnia Tue, 1 Feb 2005 23:01:46 -0500, Tom Ogilvy napisa³(a):

typo/omission

if application.countif(A1:A5000,"*" & cell.Value & "*") 0 then
should be

if application.countif(Range("A1:A5000"), _
"*" & cell.Value & "*") 0 then

now it works but unfortunately it is not what I would like it to be.

I would like to have a macro that searches the first column for the same
words or short sentence as in the second column. If the word or the short
sentence is found in the first column , it is copied and pasted to the
third column.

so the third column should be filled with some words or short sentences but
when I run the macro it filled only several cells in third column but I
would like to have something like below:

A(words) B(conditions) C (results of macro)
aaaa aa aa
bbb bb bb
cccc ee nothing
aa aa
bb bb
fff nothing
cccc nothing
ggg nothing
aaa aa
ee ee

I am sorry very much for not explaining it clear enough.

Tomek

Try this
in C2 put in a formula

=if(B2="","",if(countif(A2,"*"&B2&"*")0,B2,""))

then drag fill down column C.

--
Regards,
Tom Ogilvy

"Tomek" wrote in message
...
Dnia Tue, 1 Feb 2005 23:01:46 -0500, Tom Ogilvy napisa³(a):

typo/omission

if application.countif(A1:A5000,"*" & cell.Value & "*") 0 then
should be

if application.countif(Range("A1:A5000"), _
"*" & cell.Value & "*") 0 then

now it works but unfortunately it is not what I would like it to be.

I would like to have a macro that searches the first column for the same
words or short sentence as in the second column. If the word or the short
sentence is found in the first column , it is copied and pasted to the
third column.

so the third column should be filled with some words or short sentences
but
when I run the macro it filled only several cells in third column but I
would like to have something like below:

A(words) B(conditions) C (results of macro)
aaaa aa aa
bbb bb bb
cccc ee nothing
aa aa
bb bb
fff nothing
cccc nothing
ggg nothing
aaa aa
ee ee

I am sorry very much for not explaining it clear enough.

Tomek