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?
|