Get all wanted Values in a Column

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
2,072
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What's the best way to Find all instances of *Fred* in a worksheet column ?
Tried recording a macro, highlit column and Control-F but it only showed the Select part.
Should I set a Range first? Then iterate through that?
You can't use something like:
"Select Column1, Column2 from Sheet1 Where Column2 Like '*Fred*'"
The Hits are destined to build into a listview control.

A Google search brought up a few things but wasn't obvious which to choose. And suggested things like VLOOKUP and MATCH which may be well off track.
I could just For i= 1 to NumberOfRows: If.... Next etc. but that doesn't seem the most efficient. Or is that just a good/quick as anything else?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
This was meant as a generic question to gleam some ideas from. People here know more than I do !
 
Upvote 0
Try filtering the column using wildcards such as "*Fred*".
 
Upvote 0
VBA Code:
Option Explicit

Sub GetAllOccurences()
Dim searchRng As Range,, fnd As Variant, startCell As Range, nextFound As Range
Set searchRng = Me.Range("A:A")
Set startCell = searchRng.Find("Fred", LookIn:=xlValues, LookAt:=xlWhole)
If Not startCell Is Nothing Then
    fnd = startCell.Address
    Set nextFound = searchRng.Find("Fred", startCell, xlValues, xlWhole)
    If Not nextFound.Address = startCell.Address Then
    Do
        fnd = fnd & "," & nextFound.Address
        Set nextFound = searchRng.Find("Fred", nextFound, xlValues, xlWhole)
    Loop Until nextFound.Address = startCell.Address
    End If
End If
fnd = Split(fnd, ",")

End Sub
 
Upvote 0
Solution
Thanks Skybot. And Mumps, although I don;t know how filter is used in VBA.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Searching a range
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,223,882
Messages
6,175,164
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top