Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question VLookAllSheets excluding current sheet

    Hi,

    I'm really hoping someone can help me - I feel like I've looked all over and tried everything. I am using the following VBA Function (pulled from ozgrid.com):

    Code:
    Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
    
    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 Exit For
    
    Next wSheet
    
    Set Tble_Array = Nothing
    
    VLOOKAllSheets = vFound
    
    End Function
    But I need for it to look at all sheets in the workbook EXCEPT the one on which I'm using the function (named "2014 Master"). Please help!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    This should do the trick.

    Code:
    Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
    
       Dim wSheet As Worksheet
       Dim vFound
    
       On Error Resume Next
    
       For Each wSheet In ActiveWorkbook.Worksheets
    
            If wSheet.Name <> "2014 Master" Then  '**** Added Line ****
              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 Exit For
    
            End If     '***** Added Line ******
        Next wSheet
    
        Set Tble_Array = Nothing
        VLOOKAllSheets = vFound
    
    End Function
    HTH
    Last edited by RetiredGeek; 2013-10-30 at 16:45.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    erindemont (2013-10-30)

  4. #3
    New Lounger
    Join Date
    Oct 2013
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks!! That worked. Now I just need to figure out how to get it to automatically update when cells on the source sheets are changed... but that's another thread. Thank you so much for your help! This was driving me crazy!!

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Glad to be of assistance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •