retrieving a non-active tab name

[hancartay]

HI
I currently have a macro that searches several tabs for information but i need a way to display in a cell the tab name that the information is located on.

eg the macro returns the unique number 123. What tab is 123 on?

 

[hancartay]

Could you tell me how to apply this code and how to access it? is there a specific formula?

 

[Peter_SSs]

Press Alt+F8, select FindAllInstances, click Run

However, as I mentioned before I am not exactly sure what you have or what you are trying to achieve so this is just sample code that searches for the text "abc" in all sheets except a sheet called "Search". If it does the sort of thing you want then you will need to modify it to suit your specific requirements, or detail those requirements more to see if somebody can help further.

 

[hancartay]

The method above gives the list below

A-H1
A-I1
A-H2
A-I2
A-H3 etc

(i tried to post a pic but it didn't work)

I cannot understand it and it goes off the bottom of the screen. I'm not sure why it say A-H and A-I over and over.

I adjusted the code you gave me to look for G1 in stead of "abc" but that's the only change i made.

 

[Peter_SSs]

I adjusted the code you gave me to look for G1 in stead of "abc" but that's the only change i made.

1. Please post your adjusted code. See my signature block for how to use Code tags to preserve the indentations in the code as it makes it much easier to read & debug.

2. Are you searching for the text "G1" or for what is in cell G1 on a sheet?
If the latter, what sheet is this cell G1 in?

 

[hancartay]

Here's the code

Sub FindAllInstances()    Dim ws As Worksheet
    Dim FirstAddress As String, msg As String
    Dim Found As Range
    
    For Each ws In Worksheets
        If ws.Name <> "Search" Then
            FirstAddress = vbNullString
            With ws.UsedRange
                Set Found = .Find(What:=G1, LookAt:=xlWhole, _
                            MatchCase:=False, SearchFormat:=False)
                If Not Found Is Nothing Then
                    FirstAddress = Found.Address
                    Do
                        msg = msg & vbLf & ws.Name & "-" & Found.Address(0, 0)
                        Set Found = .FindNext(After:=Found)
                    Loop Until Found.Address = FirstAddress
                End If
            End With
        End If
    Next ws
    If Len(msg) Then
        MsgBox msg
    Else
        MsgBox "Search item not found"
    End If
End Sub

It's the contents of cell G1 on the "Search" sheet that i'm looking for, as this will change depending on what product is being searched for.

 

[Peter_SSs]

It's the contents of cell G1 on the "Search" sheet that i'm looking for

OK, that explains why you were getting a whole sheet full of meaningless answers. The code you have written is looking for the contents of a variable called G1. Since you haven't said what value that variable is holding, it will be looking through your whole workbook for blank cells. Naturally it will find a LOT!!!

One way to avoid that sort of thing happening is to have "Option Explicit" at top of each module you use in VBA - see my code below. You can force yourself to do so, and I would recommend this & do it myself. In the VB window use the Menus to go Tools|Options|Editor tab|Require Variable Declaration|OK. Then every time you create a new module the Option Explicit will automatically appear and you will be told if you have forgotton to declare a variable.

In any case, you need something like this. I've highlighted the changes.

Option Explicit

Sub FindAllInstances()
    Dim ws As Worksheet
    Dim FirstAddress As String, msg As String, sProd As String
    Dim Found As Range
    
    sProd = Sheets("Search").Range("G1").Value
    For Each ws In Worksheets
        If ws.Name <> "Search" Then
            FirstAddress = vbNullString
            With ws.UsedRange
                Set Found = .Find(What:=sProd, LookAt:=xlWhole, _
                            MatchCase:=False, SearchFormat:=False)
                If Not Found Is Nothing Then
                    FirstAddress = Found.Address
                    Do
                        msg = msg & vbLf & ws.Name & "-" & Found.Address(0, 0)
                        Set Found = .FindNext(After:=Found)
                    Loop Until Found.Address = FirstAddress
                End If
            End With
        End If
    Next ws
    If Len(msg) Then
        MsgBox msg
    Else
        MsgBox "Search item not found"
    End If
End Sub

 

[hancartay]

Sorry it took so long to reply. we've been having internet issues.

The modified code works and produces a separate window with a list of cell references for the lookup value.

is there a way to list the info itself rather than just the cell reference.

 

[Peter_SSs]

is there a way to list the info itself rather than just the cell reference.

I don't understand. That sounds to me like if you were looking for "abc" you want "abc" listed?

Further clarification/example(s) might help.

 

[hancartay]

Sorry that this is a very fragmented way of doing things.

I keep getting told to "make it do this and that's all", then something new keeps getting added later.

I need to look for the cell reference, like the list you've built for me, and the information related to that cell. the macro finds the "G1" info in the E column, is there a way to list the info from other cells on the same row?

 

[Peter_SSs]

.. the macro finds the "G1" info in the E column, is there a way to list the info from other cells on the same row?

1. What other columns?

2. Where should the list be placed? (Presumably a message box pop-up is not ideal)

3. Can you confirm that the search for the G1 value only needs to look in column E of each sheet. The previous code was looking everywhere on each sheet, so it makes sense to narrow the search area if possible.