ub FindAll() Dim fnd As String, FirstFound As String Dim FoundCell As Range, rng As Range Dim myRange As Range, LastCell As Range 'What value do you want to find (must be in string form)? fnd = "x" Set myRange = Worksheets("Tile").Columns("B:B") Set LastCell = myRange.Cells(myRange.Cells.Count) Set FoundCell = myRange.Find(what:=fnd) 'Test to see if anything was found If Not FoundCell Is Nothing Then FirstFound = FoundCell.Address Else GoTo NothingFound End If Set rng = FoundCell 'Loop until cycled through all unique finds Do Until FoundCell Is Nothing 'Find next cell with fnd value Set FoundCell = myRange.FindNext(after:=FoundCell) FoundCell.Columns("C:C").Select Selection.Copy Dim NextRow As Long NextRow = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row + 1 Sheets(3).Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues 'Add found cell to rng range variable Set rng = Union(rng, FoundCell) 'Test to see if cycled through to first found cell If FoundCell.Address = FirstFound Then Exit Do Loop 'Select Cells Containing Find Value 'rng.Select Exit Sub 'Error Handler NothingFound: MsgBox "No values were found in this worksheet" End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE