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?

 

[Norie]

How does your code search the worksheets?

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you post your code that does the search to return the "123" result?

 

[hancartay]

Welcome to the MrExcel board!

Could you post your code that does the search to return the "123" result?

Here it is.

Option Explicit


Public Function MultVlookup( _
                    FindThis As Variant, _
                    LookIn As Range, _
                    SheetRange As String, _
                    OffsetColumn As Integer, _
                    Optional ReturnAddress As Boolean = False) _
                As Variant


Dim Sheet As Worksheet
Dim strFirstSheet As String
Dim strLastSheet As String
Dim SheetArray() As String
Dim blnFirstSheet As Boolean
Dim rngFind As Range
Dim blnFound As Boolean
Dim intSheets As Integer
Dim n As Integer


'make function recalculate with all changes in data
'else it won't respond to changes in cells on the other worksheets
Application.Volatile


'make search range one column
If LookIn.Columns.Count > 1 Then
    Set LookIn = LookIn.Resize(LookIn.Rows.Count, 1)
End If


'size array to hold all worksheet names
ReDim SheetArray(ActiveWorkbook.Worksheets.Count)


'get the two worksheet names
strFirstSheet = Left(SheetRange, InStr(1, SheetRange, ":") - 1)
strLastSheet = Right(SheetRange, _
                Len(SheetRange) - InStr(1, SheetRange, ":"))
'put worksheet names in the range 'Sheet Range' into an array
blnFirstSheet = False
n = 0
For Each Sheet In ActiveWorkbook.Worksheets()
    If Sheet.Name = strFirstSheet Then
        blnFirstSheet = True
    End If
    If blnFirstSheet = True Then
        SheetArray(n) = Sheet.Name
        n = n + 1
    End If
    If Sheet.Name = strLastSheet Then
        blnFirstSheet = False
    End If
Next Sheet
'save number of sheets
intSheets = n


'search range on each worksheet in array
blnFound = False
For n = 0 To intSheets - 1
    With Worksheets(SheetArray(n)).Range(LookIn.Address)
        Set rngFind = .Find(FindThis, LookIn:=xlValues, _
                        MatchCase:=False, LookAt:=xlWhole)
    End With
    If Not rngFind Is Nothing Then
        'match found
        blnFound = True
    End If
    If blnFound = True Then Exit For
Next n


'return value
If blnFound = True Then
    If ReturnAddress = False Then
        'just return the value
        MultVlookup = rngFind.Offset(0, OffsetColumn - 1)
        Else
        'return the address
        MultVlookup = SheetArray(n) & "!" & _
            rngFind.Offset(0, OffsetColumn - 1).Address
    End If
    Else
    MultVlookup = CVErr(xlErrNA)
End If
End Function

i'm not a programmer so i can't take credit for this. It searches all the tabs in a range for as given value using =multvlookup($B$1,$D$1:$D$493,"A:Q",-2).

 

[Peter_SSs]

I think your existing function might already do what you want. The function has 5 arguments and I suspect that you have only been physically using four of them. The final argument (highlighted blue below) allows the function to return the address, including the sheet name, where the item was found if set to True.

Function MultVlookup( _
            FindThis As Variant, _
            LookIn As Range, _
            SheetRange As String, _
            OffsetColumn As Integer, _
            Optional ReturnAddress As Boolean = False) _
        As Variant

So, try the function something like this:

=MultVlookup("abc",C1:C100,"Sheet1:Sheet4",1,TRUE)

 

[hancartay]

The function you mentioned does work but it also gives the cell refeernce for the for the lookup value (123). Result = B!$A$40. I just need the B.
Can the code be edited?

 

[Peter_SSs]

The function you mentioned does work but it also gives the cell refeernce for the for the lookup value (123). Result = B!$A$40. I just need the B.
Can the code be edited?

Yes, very easily. Just delete the red part shown below from near the end of the function.

Else
'return the address
MultVlookup = SheetArray(n) & "!" & _
rngFind.Offset(0, OffsetColumn - 1).Address
End If

 

[hancartay]

That's brilliant. It works perfectly. Thank you.

 

[hancartay]

Hi

I've just had one of those "that's great, But" conversations and now i need to edit the spreadsheet some more.

I've altered the references to look for a product code across the A to Q tabs and it works in giving me the reference for the product but only for the first one it encounters. Eg I looked for product DS1957 and it found the product in tab "B" then it stopped looking.

What i need is for it to find all of the instances of the product (eg. "DS1957") and list them all, by their unique number, on the search sheet. Some products are made more than others so i don't know what size the list should be.

I believe that if i can get the list of the unique numbers that it would just be a matter of cosmetics using the above advice you've already given.

Any help would be appreciated

 

[Peter_SSs]

Sorry, I haven't been able to be on the forum much lately. I'm not entirely clear what you have or what you are trying to achieve, but see if this is some use in developing some code that does what you want. This code should search each worksheet (except one called "Search") for cells containing just "abc". The result is a listing of the sheet name and cell address where each is found.

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:="abc", 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