Retrieve last day of the month but hitting error

Hi,
it's because you have "July" in B1. My understanding was that you have Month index i.e. 7.
add this to your module

Private Function MonthIndex(strMonth As String) As Variant
    Select Case LCase(strMonth)
    Case "january"
        MonthIndex = 1
    Case "february"
        MonthIndex = 2
    Case "march"
        MonthIndex = 3
    Case "april"
        MonthIndex = 4
    Case "may"
        MonthIndex = 5
    Case "june"
        MonthIndex = 6
    Case "july"
        MonthIndex = 7
    Case "august"
        MonthIndex = 8
    Case "september"
        MonthIndex = 9
    Case "october"
        MonthIndex = 10
    Case "november"
        MonthIndex = 11
    Case "december"
        MonthIndex = 12
    Case Else
        MonthIndex = CVErr(xlErrNA)
    End Select
End Function

Amend your code as follows:

Dim Month As Integer
Dim Year As Integer
Dim EndDate As Date


Month = MonthIndex(Range("B1").Value) '7
Year = Range("D1").Value '2014
EndDate = WorksheetFunction.EoMonth(DateSerial(Year, Month, 1), 0)