Show page name when function is complete.

zrupnick · Sep 2nd 2011

I like the way this function works and I am looking for a way to show the page name when a match is found.

Code

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
    Col_num As Integer, Optional Range_look As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
    For Each wSheet In ActiveWorkbook.Worksheets
        With wSheet
        Sheet = Worksheets(wsIndex).Name
        Set Tble_Array = .Range(Tble_Array.Address)
            vFound = WorksheetFunction.VLookup _
            (Look_Value, Tble_Array, _
            Col_num, Range_look)
        End With
        If Not IsEmpty(vFound) Then Exit For
    Next wSheet
     Set Tble_Array = Nothing
    VLOOKAllSheets = vFound
 
End Function

Display More

AAE · Sep 2nd 2011

Re: Show page name when function is complete.

Welcome to Ozgrid.

Please use the correct tags when posting VBA code.
I have replaced the HTML tags with code tags.

That bit of code should have come from here: Vlookup Across Excel Worksheets
It appears to be modified and returns an error.

This revision will display a message box with the name of the sheet on which the value was found.

Code

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
    Col_num As Integer, Optional Range_look As Boolean)


''''''''''''''''''''''''''''''''''''''''''''''''


'Written by OzGrid.com
'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.


'''''''''''''''''''''''''''''''''''''''''''''''''


Dim wSheet As Worksheet
Dim vFound


On Error Resume Next
    For Each wSheet In ActiveWorkbook.Worksheets


        With wSheet


        Set Tble_Array = .Range(Tble_Array.Address)


            vFound = WorksheetFunction.VLookup _
            (Look_Value, Tble_Array, _
            Col_num, Range_look)


        End With


        If Not IsEmpty(vFound) Then
            MsgBox "Found on sheet  :" & wSheet.Name
            Exit For
        End If


    Next wSheet
    Set Tble_Array = Nothing


    VLOOKAllSheets = vFound


End Function

Display More

Show page name when function is complete.

Participate now!

Share