Finding within 1 column

  • Thread starter Thread starter Aaron C
  • Start date Start date
A

Aaron C

Hey all,
I have a database clean up project, and I'm a little stumped about how
to do it. I *could* do it manually (and have been), but there are
enoough rows (over 6000, and more each day) that it's more than
slightly daunting.
The project is to consolidate lists of programs - IE, Adobe Acrobat -
that have been typed in willy nilly, and set them up to be batch-fixed
in a database.
the problem is that I'm not sure I'm being consistent enough: I may
call it (for example) Adobe Acrobat Pro in one place, and call it
Adobe Acrobat in another place.
What i want to do is grab each word in a given cell, and compare it
against each other word in a column. so, for example, if my column
were:
*A*
-----
This thing
That place
another person whose name is irrelevant
that guy
this dog I met
....
....

then I would want to know that "This" occurs in both row 1 and row 5,
and that "that" occurs in both 2 and 4.
can anyone give me any advice on this?
Bonus points if you can filter out words that appear anywhere in
column B :-)
Thanks muchly in advance, and no, this isn't a school project.
 
Sub findthis()
myvar = "this"
With Worksheets("sheet28").Range("a1:a22")
Set c = .Find(What:=myvar, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
On Error Resume Next
Do
ms = ms & " " & c.Address
' MsgBox c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With
MsgBox myvar & " found at " & ms

End Sub
 
<snip code>
That worked very well, thanks! I had to change it a touch to make it
look up the value from the first cell (something I didn't make very
clear), and I'll change it again to make it loop for the next cell,
etc.

But my first cell overflowed the Msgbox. How many characters can one
of those hold?
 
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
<snip code>
That worked very well, thanks! I had to change it a touch to make it
look up the value from the first cell (something I didn't make very
clear), and I'll change it again to make it loop for the next cell,
etc.

But my first cell overflowed the Msgbox. How many characters can one
of those hold?
 
ms = ms & " " & Replace(c.Address, "$", "")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
<snip code>
That worked very well, thanks! I had to change it a touch to make it
look up the value from the first cell (something I didn't make very
clear), and I'll change it again to make it loop for the next cell,
etc.

But my first cell overflowed the Msgbox. How many characters can one
of those hold?
 
You did say rows.
ms = ms & " " & c.Row

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
<snip code>
That worked very well, thanks! I had to change it a touch to make it
look up the value from the first cell (something I didn't make very
clear), and I'll change it again to make it loop for the next cell,
etc.

But my first cell overflowed the Msgbox. How many characters can one
of those hold?
 
This will identify the rows for this and the rows for that

Sub findthisandthat()
For Each i In Array("this", "that")
'myvar = "this"
ms = ""
'MsgBox i
With Worksheets("sheet28").Range("a1:a22")
Set c = .Find(What:=i, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
On Error Resume Next
Do
'ms = ms & " " & Replace(c.Address, "$", "")
ms = ms & " " & c.Row
' MsgBox c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With
MsgBox i & " found at " & ms
Next i
End Sub
 
This finds rows for this and rows for that

Sub findthisandthat()
For Each i In Array("this", "that")
'myvar = "this"
ms = ""
'MsgBox i
With Worksheets("sheet28").Range("a1:a22")
Set c = .Find(What:=i, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
On Error Resume Next
Do
'ms = ms & " " & Replace(c.Address, "$", "")
ms = ms & " " & c.Row
' MsgBox c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With
MsgBox i & " found at " & ms
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
<snip code>
That worked very well, thanks! I had to change it a touch to make it
look up the value from the first cell (something I didn't make very
clear), and I'll change it again to make it loop for the next cell,
etc.

But my first cell overflowed the Msgbox. How many characters can one
of those hold?
 
Or

ms = ms & " " & c.Address(0,0)


Don said:
ms = ms & " " & Replace(c.Address, "$", "")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
<snip code>
That worked very well, thanks! I had to change it a touch to make it
look up the value from the first cell (something I didn't make very
clear), and I'll change it again to make it loop for the next cell,
etc.

But my first cell overflowed the Msgbox. How many characters can one
of those hold?
Click to expand...
 
Back
Top