Populating list of tabs in column based on using used range

Halk

In the attached file, I commented out your code and added a revision as follows (main changes in bold):

Sub ReportFinds()
    Dim sh As Worksheet, ws As Worksheet
    'Dim rng As Variant, rngCell As Range
    Dim rng As Range, rngCell As Range
    'Dim lngLstRow As Long, lngLstCol As Long
    Dim p As Long ' for row offset results

    Set sh = Sheets("sheet1")
    ' need to set as a range
    Set rng = sh.Range("C2") '.Value

    For Each ws In Worksheets
       ' forget this approach!
       ' lngLstRow = ws.UsedRange.Rows.Count
       ' lngLstCol = ws.UsedRange.Columns.Count
        ' don't check sheet1
        If ws.Name <> sh.Name Then
            ' can't use that Range method so look only in User Range for the worksheet
            For Each rngCell In ws.UsedRange 'Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
                If rng.Value = rngCell.Value Then
                    ' write sheet and address
                    rng.Offset(p, 1) = ws.Name
                    rng.Offset(p, 2) = rngCell.Address
                    ' increment counter
                    p = p + 1
                End If
            Next rngCell
        End If
    Next ws
End Sub

Now the code will list (in column D) the sheets it found the C2 search value in but also the addresses it was found (in column E). You'll see this demonstarted since I added an extra "ASD" in cell F9 of worksheet ST.

Note that I haven't written code to clear the results before the search nor to code format the results but I hope this helps.

Hello again Halk,

At the top of your code you assign a value to the variable "rng" :

rng = sh.Range("C2").Value

In the line which is throwing the error "Object Required", your code is asking VBA to get the value of a value (rng) which is not possible.

Just remove " .Value " so the line becomes:

If rng = rngCell.Value Then rng.Offset(, 1) = ws.Name

If this solves things , please mark my answer as selected.

Cheers   :-)