Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find duplicate data in two tables,then listing it? | Excel Worksheet Functions | |||
How to find duplicate data in two tables,then listing it?(Excel) | Excel Worksheet Functions | |||
Using Find & Replace to edit cell references in links | Excel Discussion (Misc queries) | |||
find all references to a particular cell | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |