Listing of cell references from a FIND All command.
 

Can the results (cell references) obtained after using "EDIT/FIND/Find All"
command be placed into a column array?

Listing of cell references from a FIND All command.
 

Let's say we are looking for "happiness" where ever it occurs on a single
sheet. Try this code:

Sub joe()
Dim listit As Range
Set listit = Nothing
x = "happiness"
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, x, 1) < 0 Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next

If listit Is Nothing Then
Else
i = 1
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub

1. it uses column A for the results
2. it only looks on a single sheet
3. it can be modified for numerical values
4. there is a danger that the results can over-write data
--
Gary''s Student - gsnu200718


"Joe" wrote:

Can the results (cell references) obtained after using "EDIT/FIND/Find All"
command be placed into a column array?

Listing of cell references from a FIND All command.
 

It works very well. Would it be possible to inlcude the total cells found and
place it at say A1 or somewhere else on the sheet. It would be nice to get a
message if zero occurrences were found. Also can "happiness" be read by the
VBA macro from a paticular cell.
Many thanks

"Gary''s Student" wrote:

Let's say we are looking for "happiness" where ever it occurs on a single
sheet. Try this code:

Sub joe()
Dim listit As Range
Set listit = Nothing
x = "happiness"
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, x, 1) < 0 Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next

If listit Is Nothing Then
Else
i = 1
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub

1. it uses column A for the results
2. it only looks on a single sheet
3. it can be modified for numerical values
4. there is a danger that the results can over-write data
--
Gary''s Student - gsnu200718


"Joe" wrote:

Can the results (cell references) obtained after using "EDIT/FIND/Find All"
command be placed into a column array?

Listing of cell references from a FIND All command.
 

Hi Joe:

Sub joe2()
Dim listit As Range
Set listit = Nothing
x = Range("A1").Value
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, x, 1) < 0 Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next
Range("A2").Value = listit.Count - 1
If listit.Count = 1 Then
MsgBox ("nothing found")
Else
i = 3
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub


Different version, different name.

1. put the searched-for value in A1
2. leave A2 empty

The macro will now put the count in A2 and start the list in A3.
--
Gary''s Student - gsnu200718


"Joe" wrote:

It works very well. Would it be possible to inlcude the total cells found and
place it at say A1 or somewhere else on the sheet. It would be nice to get a
message if zero occurrences were found. Also can "happiness" be read by the
VBA macro from a paticular cell.
Many thanks

"Gary''s Student" wrote:

Let's say we are looking for "happiness" where ever it occurs on a single
sheet. Try this code:

Sub joe()
Dim listit As Range
Set listit = Nothing
x = "happiness"
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, x, 1) < 0 Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next

If listit Is Nothing Then
Else
i = 1
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub

1. it uses column A for the results
2. it only looks on a single sheet
3. it can be modified for numerical values
4. there is a danger that the results can over-write data
--
Gary''s Student - gsnu200718


"Joe" wrote:

Can the results (cell references) obtained after using "EDIT/FIND/Find All"
command be placed into a column array?

Listing of cell references from a FIND All command.
 

This works brilliantly, however it does count the word in A1 giving a count
of one too many. I have another question. When searching for the word "in"
your macro will find it in "happiness". Can I write x=" in " in the first
macro? Or put a space before the text "in" into cell A1?

"Gary''s Student" wrote:

Hi Joe:

Sub joe2()
Dim listit As Range
Set listit = Nothing
x = Range("A1").Value
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, x, 1) < 0 Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next
Range("A2").Value = listit.Count - 1
If listit.Count = 1 Then
MsgBox ("nothing found")
Else
i = 3
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub


Different version, different name.

1. put the searched-for value in A1
2. leave A2 empty

The macro will now put the count in A2 and start the list in A3.
--
Gary''s Student - gsnu200718


"Joe" wrote:

It works very well. Would it be possible to inlcude the total cells found and
place it at say A1 or somewhere else on the sheet. It would be nice to get a
message if zero occurrences were found. Also can "happiness" be read by the
VBA macro from a paticular cell.
Many thanks

"Gary''s Student" wrote:

Let's say we are looking for "happiness" where ever it occurs on a single
sheet. Try this code:

Sub joe()
Dim listit As Range
Set listit = Nothing
x = "happiness"
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, x, 1) < 0 Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next

If listit Is Nothing Then
Else
i = 1
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub

1. it uses column A for the results
2. it only looks on a single sheet
3. it can be modified for numerical values
4. there is a danger that the results can over-write data
--
Gary''s Student - gsnu200718


"Joe" wrote:

Can the results (cell references) obtained after using "EDIT/FIND/Find All"
command be placed into a column array?

Listing of cell references from a FIND All command.
 

Yes you can. If you are searching for " in ", then put it in A1. This will
exclude finding "happiness". By the way, if you want to find cells with a
specific value only, then try joe3:


Sub joe3()
Dim listit As Range
Set listit = Nothing
x = Range("A1").Value
For Each r In ActiveSheet.UsedRange
If r.Value = x Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next
Range("A2").Value = listit.Count - 1
If listit.Count = 1 Then
MsgBox ("nothing found")
Else
i = 3
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub

Only one line of joe2 was changed to make joe3. This version will find
"joy", but will ignore "joy to the world"
--
Gary''s Student - gsnu200718

Listing of cell references from a FIND All command.
 

Thanks for that code. What would be a good reference book for an
introduction to VBA for excel since it seems to be so useful and powerful?

"Gary''s Student" wrote:

Yes you can. If you are searching for " in ", then put it in A1. This will
exclude finding "happiness". By the way, if you want to find cells with a
specific value only, then try joe3:


Sub joe3()
Dim listit As Range
Set listit = Nothing
x = Range("A1").Value
For Each r In ActiveSheet.UsedRange
If r.Value = x Then
If listit Is Nothing Then
Set listit = r
Else
Set listit = Union(listit, r)
End If
End If
Next
Range("A2").Value = listit.Count - 1
If listit.Count = 1 Then
MsgBox ("nothing found")
Else
i = 3
For Each r In listit
Cells(i, "A").Value = r.Address
i = i + 1
Next
End If
End Sub

Only one line of joe2 was changed to make joe3. This version will find
"joy", but will ignore "joy to the world"
--
Gary''s Student - gsnu200718

Listing of cell references from a FIND All command.
 

Hi Joe:

There are many good books & resources:

1. start with:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

2. practice using the Recorder

3. acquire something like:
Walkenbach's Power Programming with VBA
--
Gary''s Student - gsnu200718