Find All Instances of Value in a Range

[keith0528]

Greetings All,

I have a range of data in column A and I want to identify all instances of a number in the range. The number is actually part of a string which is alpha numeric.

example:

147258 found in 7777
123456 found in 9999
123456 found in 9999
123456 found in 9999
654321 found in 8888
654321 found in 8888
654321 found in 8888

I have 123456 in a variable TicketNum and I want to identify all instances of it. In the above example the 3 lines in red should be selected. My current way is only finding 1 at a time. Here is my code:

TicketNum = Left(sVal, InStr(1, sVal, " ") - 1)

'find all instances of value (ticketnumber)
                            
 Set rngSearch = ActiveSheet.Range("A2:A" & LastRow)
 Set rngFound = rngSearch.Find(What:=TicketNum, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
rngFound.Select

once selected, these will be cut and paste to another tab.

thanks in advance

 

[AlphaFrog]

This uses the Autofilter feature.

TicketNum = Left(sVal, InStr(1, sVal, " ") - 1)

[color=green]'find all instances of value (ticketnumber)[/color]
Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:=TicketNum & "*"

Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
    Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[keith0528]

This uses the Autofilter feature.

TicketNum = Left(sVal, InStr(1, sVal, " ") - 1)

[COLOR=green]'find all instances of value (ticketnumber)[/COLOR]
Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:=TicketNum & "*"

Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
    Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

Thanks so much AlphaFrog - That does exactly what I need.