Trying to use a month name in my VLookup

[Eric Penfold]

Trying to use a month name to vlookup but the error code 1004 comes up. What am i doing wrong with the vlookup function?

Option Explicit

Sub Target_VLookup()

    Dim SourceWb As Workbook, DestWb As Workbook
    Dim ws     As Worksheet, wsDMU As Worksheet, wsSO As Worksheet
    Dim SourceLRow As Long, LRow  As Long, Row As Long
    Dim FileToOpen As Variant
    Dim MyDate As String, MyMonth As Date
    Dim FirstDayOfMonth As Date, CurrentDate As Date
    Dim r      As Integer
    Dim SourceRng As Range, DesRng As Range

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
   
    Set DestWb = Workbooks("DailyMail.xlsx")
    MyDate = Format(Date, "mMmm")
    Set wsDMU = DestWb.Worksheets("Daily Mail Update")
    Set DesRng = wsDMU.Range("A2:D2")
   
    FileToOpen = ("\\DF-AZ-FILE01\Company\SALES\REPORTING\2024\Sales Report 24.xlsx")
    Workbooks.Open FileToOpen
   
    Set SourceWb = Workbooks("Sales Report 24.xlsx")
    Set wsSO = SourceWb.Worksheets("Sales Order")
    LRow = wsSO.Cells(Rows.Count, 2).End(xlUp).Row
    Set SourceRng = wsSO.Range("B2:M" & LRow)
   
    DestWb.Activate
    wsSO.Activate
   
    With wsDMU
        .Cells(2, 4).Value = WorksheetFunction.VLookup(MyDate, _
                   SourceRng, 13, False)
    End With
   
    SourceWb.Close
   
    With Application
       
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
   
    MsgBox "VLookup Month Target Update"
   
End Sub

 

[Special-K99]

Columns B to M in SourceRng, that's 12 columns, you're referring to the 13th column in your VLOOKUP

 

[Eric Penfold]

Sorry yes that is that issue.
But now this

 .Cells(2, 4).Value

says "Expression not defined in context"