Help with Vlookup in VBA across multiple tabs

[Jaycrone]

Hi,

I'm relatively new to VBA and having issues when using Vlookup. I'm looking to create a input box where users enter a location and it returns the address of a particular store. The workbook contains information over 12 different sheets and I want it to look through each sheet and if it doesn't find the information on the first, it moves to the next and so on.

I have used this to test against one of the sheets and it works fine:

Sub BranchAddress()

On Error GoTo MyErrorHandler:

Dim Branch_Name As String

Branch_Name = InputBox("Enter the Branch Name : ")

If Len(Branch_Name) > 0 Then
Address = Application.WorksheetFunction.VLookup(Branch_Name, Sheets("Area1").Range("C7:D42"), 2, False)
MsgBox "Branch address is : " & Address
End If

Else
MsgBox ("You have entered an incorrect branch")
End If

Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Branch address is not available"
End If


End Sub

But I cannot think how to get this to work across multiple sheets?

Any help would be great!

 

[gallen]

If that code works then this should loop through each sheet and look up the branch name, assuming the look up range is the same for each sheet:

I've tested it with dummy data and seems ok here.

Sub BranchAddress()
'IMPORTANT! >>> This assumes the lookup range is the same on each sheet
On Error GoTo MyErrorHandler:


Dim Branch_Name As String, Address As String
Dim sh As Worksheet
    
    Branch_Name = InputBox("Enter the Branch Name : ")
    For Each sh In ThisWorkbook.Worksheets
        
        If Len(Branch_Name) > 0 Then
            On Error Resume Next
            Address = Application.WorksheetFunction.VLookup(Branch_Name, Sheets(sh.Name).Range("C7:D42"), 2, False)
            On Error GoTo 0
            If Len(Address) > 0 Then
                MsgBox "Address is: " & Address
                Exit For
            End If
        Else
            MsgBox ("You have entered an incorrect branch")
        End If
    Next sh
    If Len(Address) = 0 Then MsgBox "No Address Found", vbCritical, "Error"
Exit Sub
MyErrorHandler:
    MsgBox Err.Description
End Sub

 

[Jaycrone]

If that code works then this should loop through each sheet and look up the branch name, assuming the look up range is the same for each sheet:

I've tested it with dummy data and seems ok here.

Sub BranchAddress()
'IMPORTANT! >>> This assumes the lookup range is the same on each sheet
On Error GoTo MyErrorHandler:


Dim Branch_Name As String, Address As String
Dim sh As Worksheet
    
    Branch_Name = InputBox("Enter the Branch Name : ")
    For Each sh In ThisWorkbook.Worksheets
        
        If Len(Branch_Name) > 0 Then
            On Error Resume Next
            Address = Application.WorksheetFunction.VLookup(Branch_Name, Sheets(sh.Name).Range("C7:D42"), 2, False)
            On Error GoTo 0
            If Len(Address) > 0 Then
                MsgBox "Address is: " & Address
                Exit For
            End If
        Else
            MsgBox ("You have entered an incorrect branch")
        End If
    Next sh
    If Len(Address) = 0 Then MsgBox "No Address Found", vbCritical, "Error"
Exit Sub
MyErrorHandler:
    MsgBox Err.Description
End Sub

That works perfectly thank you! Is there a way I could incorporate that into the following:

Sub BranchAddress()

On Error GoTo MyErrorHandler:

Dim Branch_Name As String

Branch_Name = InputBox("Enter the Branch Name : ")

If Len(Branch_Name) > 0 Then
    
  Det = "Address: " & Application.WorksheetFunction.VLookup(Branch_Name, Sheets("Area1").Range("C7:J42"), 2, False)
  Det = "Manager: " & Application.WorksheetFunction.VLookup(Branch_Name, Sheets("Area1").Range("C7:J42"), 3, False)
    MsgBox "Branch Details : " & vbNewLine & Det

Else
    MsgBox ("You have entered an incorrect branch")
End If

Exit Sub
MyErrorHandler:
    If Err.Number = 1004 Then
        MsgBox "Branch address is not available"
    End If
    

End Sub

As I want it to return various values such as store address and manager name.

Thanks

 

[gallen]

Straight forward, building your string:

Sub BranchAddress()
'IMPORTANT! >>> This assumes the lookup range is the same on each sheet
On Error GoTo MyErrorHandler:


Dim Branch_Name As String, Det As String
Dim sh As Worksheet
    
    Branch_Name = InputBox("Enter the Branch Name : ")
    For Each sh In ThisWorkbook.Worksheets
        
        If Len(Branch_Name) > 0 Then
            On Error Resume Next
            Det = "Details:" & vbNewLine & "Address: " & Application.WorksheetFunction.VLookup(Branch_Name, Sheets(sh.Name).Range("C7:J42"), 2, False) & vbNewLine
            Det = Det & "Manager: " & Application.WorksheetFunction.VLookup(Branch_Name, Sheets(sh.Name).Range("C7:J42"), 3, False) & vbNewLine
            'And so on, building up your Det variable to contain all details.
            On Error GoTo 0
            If Len(Det) > 0 Then
                MsgBox Det, vbInformation, "Details"
                Exit For
            End If
        Else
            MsgBox ("You have entered an incorrect branch")
        End If
    Next sh
    If Len(Det) = 0 Then MsgBox "No Address Found", vbCritical, "Error"
Exit Sub
MyErrorHandler:
    MsgBox Err.Description
End Sub

 

[L. Howard]

gallen has given you a VBA solution, are you interested in a single formula on the sheet that will also do that?

It would look something like this:

VLOOKUP(B1,INDIRECT("'"&INDEX(I2:I6,MATCH(TRUE,COUNTIF(INDIRECT("'"&I2:I6&"'!A2:A20"),B1)>0,0))&"'!A2:C20"),{2,3},0)

Howard