Putting the output of FIND (CTRL F) into a worksheet

[cfdh_edmundo]

Hi,

I was wondering if it was possible to somehow download the results of a find search (CTRL F) to Excel ?

i.e. can I take the above list (ringed in red) and somehow get it into excel ?

I've tried right clicking and also highlighting (but it only lets you highlight a certain number of rows)

Thanks,

 

[daniels012]

Where are you doing the Find Search? In Explorer?

Michael

 

[cfdh_edmundo]

Hi,

Im doing it on the Excel Sheet itself.

I open the sheet, hit CTRL F and then enter a term (eg "*=CHRIS*") and then click find all.

I then get a list of all the occurances of *=CHRIS*, with their book, sheet, name, cell, value and the formula returned. Is was this returned list I was hoping to put in an Excel sheet.

(that URL in my original message is just a screenshot showing a list returned from a CTRL F search).

thanks

 

[Milo_Minderbinder]

Here is a workaround that might work for you

you need this sheet to return the results

Split text & number.xls
	A	B	C	D	E	F
1	Book	Sheet	Name	Cell	Value	Formula
2
3
4
5
FindAllList

.

Then run this code

Sub List_FindAll_Results()

Found = 0
findc = InputBox("Insert 'FindAll' criteria")
    
Count = WorksheetFunction.CountIf(Range("A1:IV65536"), ("*" & findc & "*"))
If Count = 0 Then

MsgBox "No matches found"
Exit Sub
End If

Range("A1").Select


Cells.Find(What:=findc, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

Do Until Found = Count
Found = Found + 1
Booknm = ActiveWorkbook.Name
'MsgBox Booknm
Sheetnm = ActiveCell.Worksheet.Name
'MsgBox Sheetnm
ClAddr = ActiveCell.Address
'MsgBox ClAddr
ClValue = ActiveCell.Value
'MsgBox ClValue
ClFormula = ActiveCell.Formula
'MsgBox ClFormula

Sheets("FindAllList").Select
Range("a65536").End(xlUp).Offset(1, 0).Select
 ActiveCell.Value = Booknm
 ActiveCell.Offset(0, 1).Value = Sheetnm
 ActiveCell.Offset(0, 2).Value = ""
 ActiveCell.Offset(0, 3).Value = ClAddr
 ActiveCell.Offset(0, 4).Value = ClValue
 ActiveCell.Offset(0, 5).Value = ClFormula
    
Sheets(Sheetnm).Range(ClAddr).Select
Cells.FindNext(After:=ActiveCell).Activate
    
Loop

End Sub

I haven't included the cell name, when available.

 

[cfdh_edmundo]

Hi Milo,

I cant seem to see the sheet in your reply, it appears as textual Javascript.

Is there any chance you can upload the sheet or take a screenshot of it ?

Thanks,

 

[Milo_Minderbinder]

The sheet simply has these column headers in row 1, columns A - F

A - Book
B - Sheet
C - Name
D - Cell
E - Value
F - Formula

to look similar to the Find All output window.

The sheet is named FindAllList