none
Using VBA to create a VLOOKUP but I cannot figure out how to get the specific cell references. RRS feed

  • Question

  • Good day Excel and VBA Masters!

    I am trying to write a macro that will create a VLOOKUP in a cell. The premise seems simple but the file that I will be using for the "Table Array" and "Column Index Number" is a daily data pull which changes names... well daily.

    I have been searching Google for a way to get the cell references from the file as a string but I have not found anything that seems to fit the situation.

    I have a way to search the directory to find the file and open it, but the variable in that procedure gets the whole path. 

    Is there anyone that can show me how to accomplish this?

    Thanks

    Bill In AZ

    Thursday, February 9, 2017 6:40 PM

Answers

  • Hi ill -

    I wasn't sure if you wanted just the Filename or to use both path nd filename, so the following shows both. Also, I didn't know if your source data was using Defined Names or not. I show both of those as well in the following code. just delete & modify as appropriate for your situation. You might also want to pass the path & filename as a string into a function that returns true/false depending on successfully writing the formula. I've used a sub here just to get the idea and the crucial FormulaR1C1 statement to you.

    Option Explicit
    Public Sub BuildTheVLookup()
        Const strDataSourceSheetName As String = "Summary"
        Dim strFileNameOnly As String
        Dim strFullPathAndName As String
        Dim strPathOnly As String
        Dim strVlookupFormula As String
        Dim wksTarget As Excel.Worksheet
        
        On Error Resume Next ' Use something appropriate for you
        Set wksTarget = Excel.ThisWorkbook.Sheets("Test2")
        strFullPathAndName = "C:\Users\MyLogin\Documents\Info\Example.xlsb"                 ' Should be like "C:\Users\MyLogin\Documents\Findings\Example.xlsb"
        strFileNameOnly = Mid(strFullPathAndName, InStrRev(strFullPathAndName, "\") + 1)    ' Should be like "Example.xlsb"
        strPathOnly = Left(strFullPathAndName, InStrRev(strFullPathAndName, "\"))           ' Should be like "C:\Users\MyLogin\Documents\Findings\"
    'Note: setting the second parameter retrieves the value of the second column from the first matching row. The 3rd parameter is set to False so that the first exact match is found.
    ' Filename Only:
    '     a) If using a Defined Name for the TableArray:
        strVlookupFormula = "=VLOOKUP(""a"",'[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!ProdStaff,2,FALSE)"
        wksTarget.Range("K19").FormulaR1C1 = strVlookupFormula
    '     b) Using an R1C1 notation declaration for the TableArray. (R1C1 is the only way i've entered this type of formula):
        strVlookupFormula = "=VLOOKUP(""b"",'[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!R9C10:R15C12,2,FALSE)"
        wksTarget.Range("K20").FormulaR1C1 = strVlookupFormula
        
    ' Path and Filename:
    ' Using a different file for my convenience
        strFullPathAndName = "C:\Users\MyLogin\Documents\Findings\Example1.xlsb"
        strFileNameOnly = Mid(strFullPathAndName, InStrRev(strFullPathAndName, "\") + 1)
        strPathOnly = Left(strFullPathAndName, InStrRev(strFullPathAndName, "\"))
    
    '     c) If using a Defined Name for the TableArray:
        strVlookupFormula = "=VLOOKUP(""a"",'" & strPathOnly & "[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!ProdStaff,2,FALSE)"
        wksTarget.Range("K21").FormulaR1C1 = strVlookupFormula
    '     d) If using an a1 notation declaration for the TableArray:
        strVlookupFormula = "=VLOOKUP(""b"",'" & strPathOnly & "[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!R9C10:R15C12,2,FALSE)"
        wksTarget.Range("K22").FormulaR1C1 = strVlookupFormula
        
    End Sub

    Hope this helps you out


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Thursday, February 9, 2017 9:41 PM

All replies

  • Hi ill -

    I wasn't sure if you wanted just the Filename or to use both path nd filename, so the following shows both. Also, I didn't know if your source data was using Defined Names or not. I show both of those as well in the following code. just delete & modify as appropriate for your situation. You might also want to pass the path & filename as a string into a function that returns true/false depending on successfully writing the formula. I've used a sub here just to get the idea and the crucial FormulaR1C1 statement to you.

    Option Explicit
    Public Sub BuildTheVLookup()
        Const strDataSourceSheetName As String = "Summary"
        Dim strFileNameOnly As String
        Dim strFullPathAndName As String
        Dim strPathOnly As String
        Dim strVlookupFormula As String
        Dim wksTarget As Excel.Worksheet
        
        On Error Resume Next ' Use something appropriate for you
        Set wksTarget = Excel.ThisWorkbook.Sheets("Test2")
        strFullPathAndName = "C:\Users\MyLogin\Documents\Info\Example.xlsb"                 ' Should be like "C:\Users\MyLogin\Documents\Findings\Example.xlsb"
        strFileNameOnly = Mid(strFullPathAndName, InStrRev(strFullPathAndName, "\") + 1)    ' Should be like "Example.xlsb"
        strPathOnly = Left(strFullPathAndName, InStrRev(strFullPathAndName, "\"))           ' Should be like "C:\Users\MyLogin\Documents\Findings\"
    'Note: setting the second parameter retrieves the value of the second column from the first matching row. The 3rd parameter is set to False so that the first exact match is found.
    ' Filename Only:
    '     a) If using a Defined Name for the TableArray:
        strVlookupFormula = "=VLOOKUP(""a"",'[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!ProdStaff,2,FALSE)"
        wksTarget.Range("K19").FormulaR1C1 = strVlookupFormula
    '     b) Using an R1C1 notation declaration for the TableArray. (R1C1 is the only way i've entered this type of formula):
        strVlookupFormula = "=VLOOKUP(""b"",'[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!R9C10:R15C12,2,FALSE)"
        wksTarget.Range("K20").FormulaR1C1 = strVlookupFormula
        
    ' Path and Filename:
    ' Using a different file for my convenience
        strFullPathAndName = "C:\Users\MyLogin\Documents\Findings\Example1.xlsb"
        strFileNameOnly = Mid(strFullPathAndName, InStrRev(strFullPathAndName, "\") + 1)
        strPathOnly = Left(strFullPathAndName, InStrRev(strFullPathAndName, "\"))
    
    '     c) If using a Defined Name for the TableArray:
        strVlookupFormula = "=VLOOKUP(""a"",'" & strPathOnly & "[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!ProdStaff,2,FALSE)"
        wksTarget.Range("K21").FormulaR1C1 = strVlookupFormula
    '     d) If using an a1 notation declaration for the TableArray:
        strVlookupFormula = "=VLOOKUP(""b"",'" & strPathOnly & "[" & strFileNameOnly & "]" & strDataSourceSheetName & "'!R9C10:R15C12,2,FALSE)"
        wksTarget.Range("K22").FormulaR1C1 = strVlookupFormula
        
    End Sub

    Hope this helps you out


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Thursday, February 9, 2017 9:41 PM
  • Sorry it's taken so long to respond, The above code was a bit over my head so it has taken me a bit to figure it out. Thank you so much for providing so many options to run this.


    Thursday, February 16, 2017 3:56 PM
  • Glad to help Bill

    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. 
 For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Thursday, February 16, 2017 8:24 PM