Select Workbook and Worksheet in Vlookup Macro

T

tinydancer

New Member
Joined
Jun 15, 2016
Messages
44
I have a macro that runs a vlookup. It's tailored not to overwrite cells that already have text in them and only carry over found values to blank cells. At the moment it simply goes looking for these values on another sheet in the same workbook called "Lookup_Sheet".

Code: 
Sub Vlookup44()                                                                                                                 
Dim i As Integer                                                                                                                
Dim stVal As String                                                                                                             
Dim lastRow As Long                                                                                                            
On Error GoTo err_handler                                                                                                      
lastRow = Range("E" & Rows.Count).End(xlUp).Row                                                                                 
For i = 2 To lastRow                                                                                                           
    If IsEmpty(Range("Q" & i)) Then                                                                                            
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 13, False)     
        On Error Resume Next                                                                                                   
        Range("Q" & i) = stVal                                                                                                 
    End If                                                                                                                     
    
    If IsEmpty(Range("R" & i)) Then
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 14, False)
        On Error Resume Next
        Range("R" & i) = stVal
    End If
    
    If IsEmpty(Range("S" & i)) Then
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 15, False)
        On Error Resume Next
        Range("S" & i) = stVal
    End If
    
    If IsEmpty(Range("T" & i)) Then
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 16, False)
        On Error Resume Next
        Range("T" & i) = stVal
    End If
Next
Exit Sub
err_handler:
    MsgBox Err.Description
End Sub

What I want is to be able to run the code and let me select the file the vlookup pulls from. Essentially it would take me to My Computer and I would click on the excel workbook I want and then I would select the exact worksheet that I want to use for the vlookup. I would really appreciate any and all help I can get on this one.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
This is achievable however it will require you to know the sheet name and range. Will the sheet name always be "Lookup_Sheet"? and will it always be the same range?
 
Last edited:
Upvote 0
The workbooks to be picked from will all have different names but the sheet name within all of the workbooks will always be the same. So "Lookup_Sheet" will be in a variety of workbooks and all contain different data. The lookup range will be the same as well.
 
Upvote 0
tinydancer said:
The workbooks to be picked from will all have different names but the sheet name within all of the workbooks will always be the same. So "Lookup_Sheet" will be in a variety of workbooks and all contain different data. The lookup range will be the same as well.
Click to expand...

Hi,
just so I understand, in each workbook you open the sheet name will always be "Lookip_Sheet"?
What is the name of the sheet in the workbook you are returning data to?

Dave
 
Upvote 0
Here is your revised sub. I could only get it to work without using the application.worksheetfunction. Instead I had to create a cell named "_Result" and set the formula of that cell to get the result.

This will prompt you for your file and then get the info you need: Please note I've only done it for the first part of your sub. You can use the syntax to complete it.

Code: 
Sub Vlookup44()
Dim i As Integer
Dim stVal As String
Dim lastRow As Long


Dim sPath As String, sfileName As String, s As String


'Only way I know of doing it without opening the workbook:
'You set a cell (can be hidden) as the "Results" cell. This will hold the vLookUp info you are after.


On Error GoTo err_handler
lastRow = Range("E" & Rows.Count).End(xlUp).Row


'*********************----GET WORKBOOK PATH---***********************
sPath = GetFile("C:\Test", "Select") 'change these values as needed.*
i = InStrRev(sPath, "\") '                                          *
sfileName = Right(sPath, Len(sPath) - i) '                          *
sPath = Left(sPath, Len(sPath) - Len(sfileName)) '                  *
'********************************************************************


For i = 2 To lastRow
    If IsEmpty(Range("Q" & i)) Then
    
        '**************************************************ENTER RESULT INTO CELL THEN USE RESULT****************************************
    
        s = "=VLOOKUP(""" & Range("E" & i).Value & """,'" & sPath & "[" & sfileName & "]" & "Lookup_Sheet'!" & "E:T, 13, FALSE)"
        Range("_Result").Formula = s
        stVal = IIf(IsError(Range("_Result").Value), "Not Found", Range("_Result").Value)
        
        '********************************************************************************************************************************
        'Old line commented out stVal = Application.WorksheetFunction.VLookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 13, False)
        On Error Resume Next
        Range("Q" & i) = stVal
    End If
    
    If IsEmpty(Range("R" & i)) Then
        stVal = Application.WorksheetFunction.VLookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 14, False)
        On Error Resume Next
        Range("R" & i) = stVal
    End If
    
    If IsEmpty(Range("S" & i)) Then
        stVal = Application.WorksheetFunction.VLookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 15, False)
        On Error Resume Next
        Range("S" & i) = stVal
    End If
    
    If IsEmpty(Range("T" & i)) Then
        stVal = Application.WorksheetFunction.VLookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 16, False)
        On Error Resume Next
        Range("T" & i) = stVal
    End If
Next
Exit Sub
err_handler:
    MsgBox Err.Description
End Sub

You will also need to add this function to your code window:

Code: 
Public Function GetFile(Optional InitialFolder As String, Optional ButtonName As String) As String

'Function to return full path of file.
Dim dlgFolder As FileDialog
Set dlgFolder = Application.FileDialog(msoFileDialogOpen)
    If InitialFolder = "" Then InitialFolder = "C:\" 'deafult to C drive
    With dlgFolder
        .InitialFileName = InitialFolder
        .Title = "Please Select Workbook"
        If ButtonName <> "" Then .ButtonName = ButtonName
        If .Show = -1 Then 'user has selected a folder
            GetFile = .SelectedItems(1)
        Else
            GetFile = ""
        End If
    End With
End Function
 
Upvote 0
@dmt32 yes that is correct, and the other sheet name will always be "Current_Week".

@gallen I was having some trouble with your code, it still doesn't run. Where exactly do I put the second part of the code? Maybe that has something to do with it.
 
Upvote 0
tinydancer said:
@dmt32 yes that is correct, and the other sheet name will always be "Current_Week".

@gallen I was having some trouble with your code, it still doesn't run. Where exactly do I put the second part of the code? Maybe that has something to do with it.
Click to expand...


Not tested but give this a try and see if does what you want:

Code: 
Sub Test()
    Dim wb As Workbook
    Dim wsSource As Worksheet
   
   On Error GoTo err_handler
    If FileIsOpen(wb) Then
        Set wsSource = wb.Sheets("Lookup_Sheet")
        Vlookup44 wsSource
        ThisWorkbook.Activate
        wb.Close False
    End If
    
err_handler:
    Application.ScreenUpdating = True
    If Err > 0 Then MsgBox (Error(Err)), 16, "Error"
End Sub


Function FileIsOpen(ByRef wb As Workbook) As Boolean


    Dim FileName As Variant
    Dim Filter As String
    Dim Index As Integer
    
    Index = IIf(Application.Version < 12, 1, 2)
    
    Filter = "Workbooks 2003 (*.xls),*.xls," & _
              "Workbooks 2007 > (*.xlsx),*.xlsx," & _
              "Workbooks 2007 > (Macro Enabled) (*.xlsm),*.xlsm," & _
              "All Excel Files (*.xl*),*.xl*," & _
              "All Files (*.*),*.*"


    FileName = Application.GetOpenFilename(Filter, Index, "Open Source Workbook")


    If FileName = False Then Exit Function
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(FileName)
    FileIsOpen = True
End Function


Sub Vlookup44(ByVal sh As Object)
    Dim stVal As Variant
    Dim lastRow As Long, i As Long
    Dim wsCurrentWeek As Worksheet
    
    Set wsCurrentWeek = ThisWorkbook.Worksheets("Current_Week")
    
    lastRow = wsCurrentWeek.Range("E" & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        If IsEmpty(wsCurrentWeek.Range("Q" & i)) Then
            stVal = Application.VLookup(wsCurrentWeek.Range("E" & i).Value, sh.Range("E:T"), 13, False)
            If Not IsError(stVal) Then wsCurrentWeek.Range("Q" & i) = stVal
        End If
        
        If IsEmpty(wsCurrentWeek.Range("R" & i)) Then
            stVal = Application.VLookup(wsCurrentWeek.Range("E" & i).Value, sh.Range("E:T"), 14, False)
            If Not IsError(stVal) Then wsCurrentWeek.Range("R" & i) = stVal
        End If
        
        If IsEmpty(wsCurrentWeek.Range("S" & i)) Then
            stVal = Application.VLookup(wsCurrentWeek.Range("E" & i).Value, sh.Range("E:T"), 15, False)
            If Not IsError(stVal) Then wsCurrentWeek.Range("S" & i) = stVal
        End If
        
        If IsEmpty(wsCurrentWeek.Range("T" & i)) Then
            stVal = Application.VLookup(wsCurrentWeek.Range("E" & i).Value, sh.Range("E:T"), 16, False)
            If Not IsError(stVal) Then wsCurrentWeek.Range("T" & i) = stVal
        End If
        Err.Clear
    Next


End Sub

I have kept your VLookUp44 code largely the same so you can make any adjustments if needed.


Hope helpful

Dave
 
Upvote 0
tinydancer said:
@gallen I was having some trouble with your code, it still doesn't run. Where exactly do I put the second part of the code? Maybe that has something to do with it.
Click to expand...

The 2nd part just goes underneath the 1st part. As long as it is in the same section as the original sub it will work.

If you are having trouble, let me know at what line the code stops. I've tested it here and it works up to this point:

Code: 
If IsEmpty(Range("Q" & i)) Then
    
        '**************************************************ENTER RESULT INTO CELL THEN USE RESULT****************************************
    
        s = "=VLOOKUP(""" & Range("E" & i).Value & """,'" & sPath & "[" & sfileName & "]" & "Lookup_Sheet'!" & "E:T, 13, FALSE)"
        Range("_Result").Formula = s
        stVal = IIf(IsError(Range("_Result").Value), "Not Found", Range("_Result").Value)
        
        '********************************************************************************************************************************
        'Old line commented out stVal = Application.WorksheetFunction.VLookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 13, False)
        On Error Resume Next
        Range("Q" & i) = stVal
    End If

So if range Q&i is empty this will execute and get the info from a closed workbook
 
Upvote 0
So far neither of these have worked. @gallen when I run yours it goes up until I select the file, once I double click the excel sheet I want to use as the vlookup page it gives me the error, "Method 'Range' of object '_Global' failed".
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Need Help VBA
2
Replies
10
Views
245
Trebor76
T
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
376
Tupe77
T
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
158
kvsrinivasamurthy
K
M
  • Question
Macro to delete rows used to work fast, but now it has become slow
Replies
3
Views
106
kvsrinivasamurthy
K
R
  • Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
128
rsolanki
R

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,399
Latest member
alchavar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top