CODEWITHSUNDEEP
excelvbasearch
Peter F
Peter F

Reputation: 435

Search workbook for all matches for string

I am trying to write a vba script which will search through a multi-page workbook and return all results which contain the dataToFind string. At the moment I am stuggling with the find and findNext functions... the below seems to look through all pages as I want it to but it only returns a single result, over and over.

Below is my code. 

Function searchGrids(contract As String, pbp As String, county As String) As String()


Dim datatoFind As String
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
Dim pos As Integer
Dim endFlag As Integer

endFlag = 0

Set indGrid = Workbooks("2014NumberGrid")

On Error Resume Next
    currentSheet = ActiveSheet.Index
    datatoFind = contract & "-" & pbp
    sheetCount = indGrid.Sheets.Count

For counter = 1 To sheetCount

    indGrid.Sheets(counter).Activate
    If IsError(Cells.find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate) = False Then Exit For

Next counter

pos = InStr(ActiveCell.Value, datatoFind)

If pos > 0 Then

    MsgBox (ActiveCell.EntireRow.Cells(1, 2).Value)

End If

Do While endFlag = 0




    If pos = 0 Then

        endFlag = 1

    Else
    For counter = 1 To sheetCount
        Do While pos > 0
            indGrid.Sheets(counter).Activate
            indGrid.FindNext(ActiveCell).Activate

            pos = InStr(ActiveCell.Value, datatoFind)

                MsgBox (ActiveCell.EntireRow.Cells(1, 2).Value)

        Loop
    Next counter
    End If

Loop

Sheets(currentSheet).Activate
End Function

Thanks

P.S. Someone asked what value the function is supposed to return. At the moment, it doesn't matter. All I am trying to do is to access the spreadsheet's data so that I can play around with it. I will then be returning either a complicated string built within the function or an array of strings. Any returned variable would be built from data found within the other workbook. If there is a better approach (say, returning a range of all rows with the term inside), then I am absolutely open to that of course.

Upvotes: 0

Views: 120

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Here is a sub you may be able to adapt. The sub looks thru all the worksheets searching for the word happy

For each instance found, the sheet name, the address, and the value in column A of the found row are recorded. The information is then output:

Sub FindingData()
    Dim sh As Worksheet, v As String, r As Range, _
        msg As String
    v = "happy"

    For Each sh In Worksheets
        With sh
        For Each r In .UsedRange
            If InStr(1, r.Value, v) > 0 Then
                msg = msg & .Name & vbTab & r.Address & vbTab & CStr(r.EntireRow.Cells(1).Value) & vbCrLf
            End If
        Next r
        End With
    Next sh

    MsgBox msg
End Sub

NOTE: I am using a loop rather than the .FIND() method.

Upvotes: 2

Related Questions