CODEWITHSUNDEEP
excelvba
isaiah
isaiah

Reputation: 331

How to Search Specific Column In Each Worksheet of Excel

I have an excel document with over 50 worksheets all with a similar naming convention. As this will be so unfriendly for users to navigate to, I wrote a VBA macro which creates a worksheet called summary with the list of all the worksheets hyperlinked in a tabular form with Sheet A B and C as the Column and Sheet 1 and 2 as rows.

Now I am trying to go through each row of a specific column in Sheet 1 and Sheet 2 and look for any reference to SheetB, SheetC and SheetD and for each reference found and I want to mark that creating a matrix.

I am not sure how to achieve this. Any assistance will be much appreciated.

I have managed to search Sheet 1 and 2 for any reference to SheetB as shown below but I am not sure how to update the corresponding cell in my summary sheet.

Function findWord(word As String, wSheet As String) As Boolean

    Dim LastRow As Long
    Dim i As Long
    LastRow = Worksheets(wSheet).Cells(Rows.Count, "D").End(xlUp).Row

    For i = LastRow To 1 Step -1
       If Worksheets(wSheet).Range("D" & i).Value = word Then
          findWord = True
          Exit Function
       End If
    Next i
End Function


For Each wsSheet In wbBook.Worksheets
    If (wsSheet.Name <> wsActive.Name) And (Left(wsSheet.Name, 4) <>   "fact") Then
    For i = 2 To lastColumn
         MsgBox wsSheet.Name
       If findWord(columnNames(counter2), wsSheet.Name) Then
          'Update summary sheet
       End If
       counter = counter2 + 1
    Next i

End If
Next wsSheet

Upvotes: 1

Views: 2222

Answers (1)

kolcinx
kolcinx

Reputation: 2233

If the result in "Summary sheet" you are looking for is similar to this :
Table on Summary sheet

Then you can use something like this (read the comments inside the code for explanations) 

Sub MarkReferencesToSheets()

    Dim wsSummary As Worksheet 'sheet with summary table matrix
    Dim wsSheetRow As Worksheet 'sheets in which we will search references to other sheets
    Dim strSheetColumnName As String 'name of the reference we are looking for
    Dim intSheetRow As Integer 'for loop purposes
    Dim intSheetColumn As Integer 'for loop purposes

    Set wsSummary = Sheets("Summary")

    For intSheetRow = 2 To 3 'change to suit; headers for rows in summary sheet
        Set wsSheetRow = Worksheets(wsSummary.Cells(intSheetRow, 1).Value)
        For intSheetColumn = 2 To 4 'change to suit; headers for columns in summary sheet
            strSheetColumnName = wsSummary.Cells(1, intSheetColumn) 'name of sheet we are looking for
            If Not wsSheetRow.Columns(4).Find(strSheetColumnName) Is Nothing Then 'look only in column "D", or 4
                wsSummary.Cells(intSheetRow, intSheetColumn) = "X" ' if we found it, mark it
            Else
                'if you want something else in the cell when reference is not found, put it here
            End If
        Next intSheetColumn
    Next intSheetRow

End Sub

Upvotes: 1

Related Questions