FormulaR1C1 Syntax re. paramaters as dynamic objects/variables

[idlewyld89]

Hello all,

My overall goal here is to preform a VLOOKUP of data from an external file (user-defined) but to allow the parameters of the VLOOKUP function to be dynamic as different users have differently formatted directories.

Below is what I believe to be the relevant code:

Global variable declarations:

Dim GMDirectoryWorkbook As WorkbookDim GMDirectoryMainSheet As Worksheet
Dim GMDirectoryTable As ListObject
Dim GMDirectoryTableName As String

Routine prompting user to select their directory file (also defines WB Object variable, WS Object Variable & ListObject Variable)

Private Sub GMDirectoryReference()' References: TestWBStatus
' Dependents: NoREV_tool,


' ~~> Prompts user to select their GM Directory, opens the file, and saves references to the Workbook, Worksheet & ListObject containing their directory
' ~~> Error Handling: File Selection (on Cancel, END Sub) - File Confirmation (on No, RESET FileDialog / on Cancel, END Sub)


Dim fd As FileDialog
Dim DataPath As String
Dim MsgResponse As Integer
Dim FileSelection As Integer
Dim TestWorksheet As Worksheet


Set ReportWbk = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select Your Directory"
    fd.InitialView = msoFileDialogViewSmallIcons
    fd.Filters.Clear
    fd.Filters.Add "Excel files", "*.xlsx;*.xls;*.xlsm"
    fd.ButtonName = "Set Reference"


Do
    FileSelection = fd.Show
    
    If FileSelection <> -1 Then
        
        MsgBox "No File Selected" & vbNewLine & vbNewLine & "Please reference your GM Directory", vbOKOnly, "Warning"
        End
    
    Else
        
        DataPath = Right$(fd.SelectedItems.Item(1), Len(fd.SelectedItems.Item(1)) - InStrRev(fd.SelectedItems.Item(1), "\"))
        MsgResponse = MsgBox("Please confirm your selected file:" & vbNewLine & vbNewLine & DataPath, vbYesNoCancel, "File Confirmation")
        
        If MsgResponse = vbYes Then
            
            If TestWBStatus(DataPath) = False Then
                
                Set DataWbk = Workbooks.Open(DataPath)
            
            End If
            
            Set GMDirectoryWorkbook = Workbooks(DataPath)
            GMDirectoryWorkbook.Activate
            Workbooks(DataPath).Application.WindowState = xlMinimized
        
        ElseIf MsgResponse = vbCancel Then
            
            MsgBox "No File Selected" & vbNewLine & vbNewLine & "Please reference your GM Directory", vbOKOnly, "Warning"
            End
        
        End If
    
    End If


Loop While MsgResponse = vbNo


On Error Resume Next


For Each TestWorksheet In GMDirectoryWorkbook.Worksheets


    Set GMDirectoryTable = TestWorksheet.ListObjects("GMDirectory")
    
    If Not GMDirectoryTable Is Nothing Then
    
        Set GMDirectoryMainSheet = TestWorksheet
        Exit For
        
    End If
    
Next TestWorksheet


End Sub

Test function to reference column index w/in directory dynamically:

Private Function ReturnHeaderIndex(ByVal WB As Workbook, ByVal TableName As String, ByVal Criteria As String) As Integer' References:
' Dependents:


' ~~> Tests for and return column index (absolute) within a defined table independent of sheet location


Dim WS_local As Worksheet


On Error Resume Next


ReturnHeaderIndex = 0


For Each WS_local In WB.Worksheets


    Set GMDirectoryTable = WS_local.ListObjects(TableName)
    
    If Not GMDirectoryTable Is Nothing Then
    
        ReturnHeaderIndex = WS_local.ListObjects(TableName).ListColumns(Criteria).Range.Column
        GMDirectoryWorksheetName = WS_local.Name
        Exit For
        
    End If
    
Next WS_local


End Function

The following line is what I'm trying to figure out how to code correctly... Every attempt thus far has yielded either incorrect data, or no data at all. That said, I've never received an error... it just doesn't reference correctly.

Cells(2, fillcol).FormulaR1C1 = "=VLOOKUP(RC[1], " + GMDirectoryWorkbook.GMDirectoryMainSheet.GMDirectoryTable + ", " + ReturnHeaderIndex(GMDirectoryWorkbook, GMDirectoryTableName, ColumnNames(1)) + ", FALSE)"