Export list of search results

**Rob007** · 05-22-2013, 02:33 PM

Hi there,

I am new at this forum and I have a (simple?) question that I could not find on the forum:

I want to know how many times a particular word occurs in an excel file. So I use ctrl F to look for that word and I can get a list. Now I want to copy that list into excel/word. How do I manage this?

I hope someone can help me with this!

Kind regards,
Rob Nip

**AlvaroSiza** · 05-22-2013, 03:23 PM

Do you want the total count of times something occurs? The address of where it's occurring? As written, it seems as though your looking for a list that will be (n) instances of the same thing, which I am finding a difficult time understanding how this would be beneficial.

Test this on a copy.

Sub FindCount()

Dim wb As Workbook
Dim ws As Worksheet
Dim strFind As String
Dim c As Range
Dim rCell As Range
Dim k As Long

strFind = InputBox("Please enter the string you are looking to find.", "Please enter string")

k = 0
For Each ws In ThisWorkbook.Sheets
    With ws.UsedRange
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            k = k + 1
        Do
            Set c = .FindNext(c)
            k = k + 1
        Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
Next ws

MsgBox k - Sheets.Count
End Sub

**Rob007** · 05-22-2013, 03:45 PM

Hi AlvaroSize,

the macro you gave works fine, thanks it counts the number of hits. But I am looking for a list incl the adress (cell + sheet). Is that also possible?

Thanks a lot for helping me out!

**AlvaroSiza** · 05-22-2013, 03:48 PM

Where would you like this list? In a new worksheet? In an existing worksheet at a specific location? Would the list, comprised of two columns, one Sheet Name, one Cell Address) suffice. Please explain precisely what you are after.

**Rob007** · 05-22-2013, 03:58 PM

Hi AlvaroSize,

I have a huge excel file, in this file there is 1 sheet with raw data, and there are 70+reporting sheets. This reporting sheets ar vlookup based on the word KPI001, KPI002 etc which they get from the sheet with raw data.

So I want to know in which reporting sheet which KPI (KPI001 - KPI700) is looked for. So when I can have an overview in a seperate sheet that gives KPI001 - Sheet name and cell then I am very pleased!

Thanks a lot!

**AlvaroSiza** · 05-22-2013, 09:50 PM

Can you provide perhaps one sheet so I can see how the data is arranged?

**Rob007** · 05-23-2013, 02:29 PM

Hi AlvaroSiza,

thanks a lot for your help so far. Today a frined of mine found the following tool on the internet

http://www.nirsoft.net/utils/sysexp.html
The tool makes it possible tot copy past text from a message box in excel to word.

So my worries are over