Trying to use index match method

[Eric Penfold]

All this works correct except for the Index match method say`s run time error 438?
Anyone know why?

Option Explicit
Sub VLookup()

    Dim SrcReD As Workbook, Alton As Workbook, Cov As Workbook, Basildon As Workbook, wb As Workbook, BName As Workbook
    Dim ws     As Worksheet, SrcRed_ws As Worksheet
    Dim SrcLRow As Long, wsLCol As Long, col_wsRed As Long, ColOn As Long, ColRd As Long
    Dim r      As Integer, c As Integer
    Dim LRow   As Long, LCol As Long
    Dim Answer As Long
    Dim FileToOpen As Variant, arrRng As Variant
    Dim SrcRed_Rng As Range, DesRng As Range, Rng As Range, TableArray As Range
    Dim BlCell As Boolean
    Dim myDate As String
    Dim Result As Variant
    BlCell = False
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Answer = MsgBox("BO Report?", vbYesNo + vbQuestion, "Is it a BO Report")
    If Answer = vbNo Then Exit Sub
    If Answer = vbYes Then
        
        Depot_Name wb
        
        Set BName = wb
        
        FileToOpen = ("S:\PURCHASING\Stock Control\Reports\Back Order Admin\Back Order Release Date.xlsx")
        Workbooks.Open FileToOpen
        Set TableArray = ActiveWorkbook.ActiveSheet.Range("A:M")
        Set SrcReD = Workbooks("Back Order Release Date.xlsx")
        Set SrcRed_ws = SrcReD.Sheets("Sheet1")
        SrcLRow = SrcRed_ws.Cells(Rows.Count, 1).End(xlToLeft).Row
        Set SrcRed_Rng = SrcRed_ws.Range("A2:B" & SrcLRow)
        wb.Activate
        myDate = Format(Date, "mmm")
        Set ws = wb.Worksheets(myDate)
        Set Rng = ws.Range("1:1")
        ColOn = WorksheetFunction.Match("Order Number", Rng, 0)
        ColRd = WorksheetFunction.Match("Back Order Release Date", Rng, 0)
        LRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
        LCol = ws.Cells(Columns.Count, 2).End(xlUp).Column
        
        If ws.Name <> "Summary" And ws.Name <> "Trend" And ws.Name <> "Supplier BO" And ws.Name <> "Diff Depot" _
            And ws.Name <> "BO Trend WO" And ws.Name <> "BO Trend WO 2" And ws.Name <> "Different Depot" Then
            
            For r = 1 To LRow
                For c = 1 To LCol
                    
                    ws.Cells(r, c).Value = WorksheetFunction.VLookup(ws.Cells(r, 3), _
                                 TableArray.WorksheetFunction.Match(SrcRed_Rng.Cells(1, c), Rng, 0), 0)
                Next c
            Next r
            
            ws.Range("N2:N" & LRow) = Application.WorksheetFunction.Days360(Range("A"), Range("M"), False)
            
        End If
        
        SrcReD.Close
        
    End If
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

 

[RoryA]

You can't use TableArray.WorksheetFunction since a range doesn't have a WorksheetFunction method.

Also, I suspect this:

SrcLRow = SrcRed_ws.Cells(Rows.Count, 1).End(xlToLeft).Row

was meant to use xlUp not xlToLeft, which is pointless.

 

[Eric Penfold]

Ok thanks all altered. But still not working same error.

 

[RoryA]

What did you alter it to?

FYI it's also not generally a good idea to name your routines the same as Excel methods

 

[RoryA]

More issues: Range("A") and Range("M") are unlikely to be correct unless those are the names that you gave to two ranges?

 

[Eric Penfold]

What did you alter it to?

FYI it's also not generally a good idea to name your routines the same as Excel methods

I`ve changed the code name. And altered the Table array to a range. Also changed the Range "A" and Range "M" to the names given.

 

[Eric Penfold]

Here is Watch window as you can see it`s correct? So struggling still say`s error 438??

 

[RoryA]

The table array already was a range. What I said was that TableArray.WorksheetFunction.Match is invalid (and will cause a 438 error) because the worksheetfunction method doesn't belong to the Range object. I think it's just a typo and that first dot should be a comma:

WorksheetFunction.VLookup(ws.Cells(r, 3), _
                                 TableArray, WorksheetFunction.Match(SrcRed_Rng.Cells(1, c), Rng, 0), 0)