Make vlookup VBA to search through all available sheet on workbook-CodePudding

how can I make Vlookup search all the available sheets in my workbook? The number of my sheets not always the same

here's the code I use but still found error on it

sub lookup()

Dim ws As Worksheet
Dim MasterSheet As Worksheet
Dim DepSheet As Worksheet
Dim form As String
Dim startcell As Range
Dim thiswb As Workbook

Set MasterSheet = Sheets("Recap")
Set DepSheet = Sheets("Depreciation")
Set startcell = MasterSheet.Range("C6")
Set thiswb = ThisWorkbook

For Each ws In thiswb
If Not ws.Name = MasterSheet.Name And Not ws.Name = DepSheet.Name Then
startcell.form = Application.WorksheetFunction.VLookup(MasterSheet.Range("C6"), ws.Range("E6:E2000"), 4, False)

If Err.Number <> 0 Then
startcell.form = Application.WorksheetFunction.VLookup(MasterSheet.Range("C6"), ws.Range("E6:E2000"), 2, False)
End If
End If

Next ws
end sub

this is the old code of the index match that I always ask the unavailable sheet from the formula.

'COA
Sheets(2).Range("A6").FormulaR1C1 = _
"=IFERROR(INDEX('ACF'!R6C:R2000C,MATCH(RC3,'ACF'!R6C3:R2000C3,0)),IFERROR(INDEX('ACF'!R6C:R2000C,MATCH(RC3,'ACF'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('BIGBIKE'!R6C:R2000C,MATCH(RC3,'BIGBIKE'!R6C3:R2000C3,0)),IFERROR(INDEX('BIGBIKE'!R6C:R2000C,MATCH(RC3,'BIGBIKE'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('BOD'!R6C:R2000C,MATCH(RC3,'BOD'!R6C3:R2000C3,0)),IFERROR(INDEX('BOD'!R6C:R2000C,MATCH(RC3,'BOD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('CORPSEC'!R6C:R2000C,MATCH(RC3,'CORPSEC'!R6C3:R2000C3,0)),IFERROR(INDEX('CORPSEC'!R6C:R2000C,MATCH(RC3,'CORPSEC'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('DIGITAL'!R6C:R2000C,MATCH(RC3,'DIGITAL'!R6C3:R2000C3,0)),IFERROR(INDEX('DIGITAL'!R6C:R2000C,MATCH(RC3,'DIGITAL'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('GDG'!R6C:R2000C,MATCH(RC3,'GDG'!R6C3:R2000C3,0)),IFERROR(INDEX('GDG'!R6C:R2000C,MATCH(RC3,'GDG'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('HC3'!R6C:R2000C,MATCH(RC3,'HC3'!R6C3:R2000C3,0)),IFERROR(INDEX('HC3'!R6C:R2000C,MATCH(RC3,'HC3'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('HRGA'!R6C:R2000C,MATCH(RC3,'HRGA'!R6C3:R2000C3,0)),IFERROR(INDEX('HRGA'!R6C:R2000C,MATCH(RC3,'HRGA'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('IT'!R6C:R2000C,MATCH(RC3,'IT'!R6C3:R2000C3,0)),IFERROR(INDEX('IT'!R6C:R2000C,MATCH(RC3,'IT'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('LDD'!R6C:R2000C,MATCH(RC3,'LDD'!R6C3:R2000C3,0)),IFERROR(INDEX('LDD'!R6C:R2000C,MATCH(RC3,'LDD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('MCD'!R6C:R2000C,MATCH(RC3,'MCD'!R6C3:R2000C3,0)),IFERROR(INDEX('MCD'!R6C:R2000C,MATCH(RC3,'MCD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('MSD'!R6C:R2000C,MATCH(RC3,'MSD'!R6C3:R2000C3,0)),IFERROR(INDEX('MSD'!R6C:R2000C,MATCH(RC3,'MSD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('NPD'!R6C:R2000C,MATCH(RC3,'NPD'!R6C3:R2000C3,0)),IFERROR(INDEX('NPD'!R6C:R2000C,MATCH(RC3,'NPD'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('PARTS'!R6C:R2000C,MATCH(RC3,'PARTS'!R6C3:R2000C3,0)),IFERROR(INDEX('PARTS'!R6C:R2000C,MATCH(RC3,'PARTS'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('PURCHASING'!R6C:R2000C,MATCH(RC3,'PURCHASING'!R6C3:R2000C3,0)),IFERROR(INDEX('PURCHASING'!R6C:R2000C,MATCH(RC3,'PURCHASING'!R6C5:R2000C5,0))" & _
",IFERROR(INDEX('TSD'!R6C:R2000C,MATCH(RC3,'TSD'!R6C3:R2000C3,0)),INDEX('TSD'!R6C:R2000C,MATCH(RC3,'TSD'!R6C5:R2000C5,0))" & _
")))))))))))))))))))))))))))))))"

this is the old code for the vlookup that I use before and excel always ask for the unavailable sheet to be updated.

'Status
Sheets(2).Range("D6").FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC3,'ACF'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'ACF'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'BIGBIKE'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'BIGBIKE'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'BOD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'BOD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'CORPSEC'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'CORPSEC'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'DIGITAL'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'DIGITAL'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'GDG'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'GDG'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'HC3'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'HC3'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'HRGA'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'HRGA'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'IT'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'IT'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'LDD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'LDD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'MCD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'MCD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'MSD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'MSD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'NPD'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'NPD'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'PARTS'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'PARTS'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'PURCHASING'!R6C3:R2000C12,4,0),IFERROR(VLOOKUP(RC3,'PURCHASING'!R6C5:R2000C12,2,0)" & _
",IFERROR(VLOOKUP(RC3,'TSD'!R6C3:R2000C12,4,0),VLOOKUP(RC3,'TSD'!R6C5:R2000C12,2,0)" & _
")))))))))))))))))))))))))))))))"

CodePudding user response：

Here is a sub to loop through all sheets.
You then need to define which sheets are which. You may want to start the loop at 3 if 1 & 2 are static sheets "Recap" and "Depreciation".

      Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name

         Next I

      End Sub

CodePudding user response：

Please, try the next way. WorksheetFunction.VLookup returns a fatal error, but Application.VLookup does not. Not understanding where you want returning, the adapted code only writes in Immediate Window the Vlookup result for each sheet. In order to see the return, use Ctrl G, being in VBE (Visual Basic for Applications Editor):

Sub lookup()
 Dim ws As Worksheet, MasterSheet As Worksheet
 Dim startcell As Range, thiswb As Workbook
 Dim result As Variant, retCell as range

 Set MasterSheet = Sheets("Recap")
 Set startcell = MasterSheet.Range("C6")
 Set retCell = MasterSheet.Range("E6") 'the cell where to return
 Set thiswb = ThisWorkbook

 For Each ws In thiswb.Worksheets
    If ws.Name <> "Recap" And ws.Name <> "Depreciation" Then
        result = Application.VLookup(startcell.value, ws.Range("E6:E2000"), 4, False)
        
        If IsError(result) Then
            result = Application.VLookup(startcell.value, ws.Range("E6:E2000"), 2, False)
        End If
        Debug.Print ws.Name, result
        if not iserror(result) then retCell.Value = result 'as stated in your comment...
    End If
 Next ws
End Sub

Your question is a little strange, in terms of returning way. If the code will work using yours as model, the result will be the Vlookup result of only the last sheet. Even if is #N/A(not a match)...

The above code returns the last occurrence, if many of the Vlookup result, which is not #N/A. Error 2042 means that Vlookup did not find a match. The equivalent of #N/A...