Index-Matching Another Workbook - Match Lookup_Value not Working

[DavieJ]

Hi there,

I've been trying to do an Index-Match that references another workbook, and I've been running into problems with the Match lookup_value. It keeps coming in as text with an apostrophe on either side when embedded in the Index-Match. When done outside of the Index-Match formula, however, it works great.

The column for the lookup_value in the current file worksheet ("CurrFileWS") needs to be variable because of columns of a current file worksheet are not always going to be arranged in a consistent order.

Example of how the formula is populating in the cell:
=INDEX('S:\...directory...\[TripsMaster.xlsx]TripsMaster'!$B$2:$B$9999,MATCH('X2','S:\...directory...\[TripsMaster.xlsx]TripsMaster'!$D$2:$D$9999,0))

When I remove the apostrophes the formula works great, so I am confident that it is how the Match's lookup_value is being populated.

Dim SearchRange As Range


Dim ColNoFound As Single 'Numerical value that corresponds to a column that has been found (e.g. column "B" = 2)


Dim TripNum As String


Dim FormulaCellTripsNum As String


Dim LastRow As Single


Dim CurrFileName As String
Dim CurrFileWS As String
Dim RefFileDir As String
Dim RefFileName As String
Dim RefFileWS As String


TripNum = "Trip Number"


'Determining the last row in the current worksheet
LastRow = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
If ActiveSheet.Range("B1").CurrentRegion.Rows.Count > LastRow Then
    LastRow = ActiveSheet.Range("B1").CurrentRegion.Rows.Count
ElseIf ActiveSheet.Range("C1").CurrentRegion.Rows.Count > LastRow Then
    LastRow = ActiveSheet.Range("C1").CurrentRegion.Rows.Count
End If


CurrFileWS = ActiveSheet.Name
CurrFileName = Sheets(CurrFileWS).Range("A1").Parent.Parent.Name
RefFileDir = "S:\...directory...\"
RefFileName = "TripsMaster.xlsx"
RefFileWS = "TripsMaster"




'Adding in "Route Number", "Route Description", and "Trip Description"
Set SearchRange = Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A1:FG1").Find(TripNum, , xlValues, xlWhole)
If Not SearchRange Is Nothing Then
    ColNoFound = SearchRange.Column
    
     'Workbooks.Open (RefFileDir & RefFileName)
    
    'Route Number
    Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A1:A" & LastRow).Offset(0, ColNoFound - 1).EntireColumn.Insert
    Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A1").Offset(0, ColNoFound - 1).Value = "Route Number" 'Printing column heading
    


    FormulaCellTripsNum = (Split(Cells(1, ColNoFound + 1).Address, "$")(1) & "2")
    Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2").Offset(0, ColNoFound - 1).Formula = "=" & FormulaCellTripsNum 'Testing reference
    
    Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2").Offset(0, ColNoFound - 1).Formula = "=INDEX('" & RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("B2:B9999").Address(1, 1, xlR1C1) & _
        ",MATCH(" & FormulaCellTripsNum & ",'" & _
        RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("D2:D9999").Address(1, 1, xlR1C1) & ",0))"
    Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2:A" & LastRow).Offset(0, ColNoFound - 1).FillDown
    
     'Workbooks(RefFileName).Close
    
Else
    MsgBox "Unable to find Trip Number column"
End If

First-time poster, so please let me know if I'm missing anything in terms of posting procedure. Any help would be greatly appreciated.

David

 

[DavieJ]

Hi again,

Wondering if anyone has had a chance to look at this yet. I would be great grateful for any assistance.

To clarify the problem, it is that the Lookup_Value of the Index-Match is populating with apostrophes.

Lookup_Value cell reference variable:

[COLOR=#ff0000]FormulaCellTripsNum [/COLOR]= (Split(Cells(1, ColNoFound + 1).Address, "$")(1) & "2")

The cell is populated correctly when the cell reference is written without an Index-Match:

Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2").Offset(0, ColNoFound - 1).Formula = "=INDEX('" & RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("B2:B9999").Address(1, 1, xlR1C1) & _
        ",MATCH(" & [COLOR=#FF0000]FormulaCellTripsNum [/COLOR] & ",'" & _
        RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("D2:D9999").Address(1, 1, xlR1C1) & ",0))"

Cell value: 34

The cell is NOT populated correctly with the Index-Match:

Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2").Offset(0, ColNoFound - 1).Formula = "=INDEX('" & RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("B2:B9999").Address(1, 1, xlR1C1) & _        ",MATCH(" & [COLOR=#ff0000]FormulaCellTripsNum  [/COLOR]& ",'" & _
        RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("D2:D9999").Address(1, 1, xlR1C1) & ",0))"

Cell value: =INDEX('S:\...directory...\[TripsMaster.xlsx]TripsMaster'!$B$2:$B$9999,MATCH('W2','S:\...directory...\[TripsMaster.xlsx]TripsMaster'!$D$2:$D$9999,0))

The Index-Match pulls correctly if the apostrophes are manually dropped after the macro has run.

Thanks,

David

 

[DavieJ]

I was able to solve the issue by 1) setting the formula equal to a stringed variable; 2) setting the cell equal to that stringed variable; 3) setting the cell formula equal to the contents of the cell nested in two replace functions -- one for "MATCH('" with "MATCH(" and the other for "'," with ",".

Thanks to anyone who gave this some thought and was unable to come up with a viable solution.

Dim FormulaString

FormulaString = "=INDEX('" & RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("B2:B9999").Address(1, 1, xlR1C1) & _ ",MATCH(" & FormulaCellTripsNum & ",'" & _
RefFileDir & "[" & RefFileName & "]" & RefFileWS & "'!" & Range("D2:D9999").Address(1, 1, xlR1C1) & ",0))"

Application.Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2").Offset(0, ColNoFound - 1) = FormulaString

Workbooks(CurrFileName).Worksheets(CurrFileWS).Range("A2").Offset(0, ColNoFound - 1).Formula = Replace(Replace(ActiveSheet.Range("A2").Offset(0, ColNoFound - 1).Formula, "MATCH('", "MATCH("), "',", ",")

Cheers,

David