Searching within spreadsheet

[Sara R.]

Is there a way to search for a specific word or letters within a cell and copy the contents of that cell to another location? For instance, I'm looking for cells within a spreadsheet and within one column that contain AE. An example of a cell with AE in it would be:

.ASSY, AE3010-32X224-7.6mm-422-120V

The amount of letters in front of AE or behind it can vary. I want to find all of the cells that contain AE and copy the contents to a column in another worksheet. Any ideas??

Thanks!

 

[Richard Schollar]

Hi Sara

You're looking at a VBA solution here to do this - is this OK? Also, do you want the search to be case sensitive or Case-insensitive (ie do you want AE to be different from ae or aE etc)?

 

[Sara R.]

Yeah, a VBA solution would be fine if you tell me how to do it I've never done it before. I definitely need it to be able to find all forms of AE - so case insensitive.

Thanks!

 

[MarkAndrews]

You could try this?

(A1 being the cell where .ASSY, AE3010-32X224-7.6mm-422-120V
is located)


=IF(COUNTIF(A1,"*AE*"),A1,"")

 

[Richard Schollar]

Sara

Give the following a try - it needs to go into a standard module in the workbook and you need to change the sheet name in the place indicated to whatever is appropriate.

Sub Get_Data()
Dim rAll As Range, rData As Range, sSearchString As String, ws As Worksheet
sSearchString = "AE"
With Sheets("Sheet2") 'change as appropriate
   Set rAll = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
With rAll
   .AutoFilter Field:=1, Criteria1:="*" & sSearchString & "*"
   If .Find(What:=sSearchString, LookAt:=xlPart, MatchCase:=False) Is Nothing Then
      MsgBox "Searched for term " & sSearchString & " not found!"
      .AutoFilter
      Exit Sub
   Else
      Set rData = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
      Set ws = Worksheets.Add
      rData.Copy Destination:=ws.Range("A1")
      .AutoFilter
   End If
End With
End Sub