Previous Topic Next Topic

Excel search and save problem

Closed
Sharon - Feb 8, 2012 at 06:19 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Feb 14, 2012 at 10:38 AM
Hello,

I'm a total Excel noobie so I hope you can help me, I will try and be as brief as posible.

Each week I will be getting an Excel CSV of 400,000 - 600,000 program names in a single column, which end in .com .exe .bat .txt etc

I need to put all the *.txt names into a new column.

At the moment I use Sort & Filter - Filter -Text Filters - Contains *.txt
( This gets me visualy what is wanted, but I cannot save the results )

By the way I'm using 2007

Thanks for any help you can give me.

Sharon




Related:

1 reply

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Feb 14, 2012 at 10:38 AM
Hi Sharon,

Start by giving column A, the column with 400,000 - 600,000 program names, a header. Now select cell A1 and apply autofilter.

Now run the code below.
Excel will ask you to input a search value.
Type in *.exe for example and hit OK.
Excel will put the search value in B1 and the result below that.
Excel will now ask if there are more extentions to look for.

I hope you like the code: 
Sub Test()
Dim x, y
Dim nCol, lRow, z As Integer

Start1:
x = InputBox("What is the extention? (example: *.exe)", "Copy data to next column")
z = 0
Do
z = z + 1
If Cells(1, z).Value = "" Then nCol = z
Loop Until Cells(1, z).Value = ""

Selection.AutoFilter Field:=1, Criteria1:=x
Cells(1, nCol).Value = x

lRow = Range("A" & Rows.Count).End(xlUp).Row
If lRow <> 1 Then Range("A2:A" & lRow).Copy Destination:=Cells(2, nCol)

y = MsgBox("Are there more extentions to look for?", vbYesNo)
If y = vbYes Then GoTo Start1

Selection.AutoFilter Field:=1

End Sub


Bestr egards,
Trowa
0