function run on the immediate window but not on cell sheet

Question

Can somebody help me to understand where is the problem, I want simply the value from each carrier from end of any month. The loop method is not a problem, this is my function:

 Function Tmese (c As String, m As Integer) As Integer 'C as Carrier, as m month
        Dim y As Date, x As Variant
         y = CDate (1 & "/" & m + 1 & "/" & 2016) - 1 'to have the end of the month add one month to c and I subtract one day
         With Worksheets (c)
             Set x = .Columns (1) .Find (y,,, xlWhole)
             If X Is Nothing Then
                 Tmese = .Cells (x.Row, 5) 'found max dates into a month to retreive corrispondence fair
             else
                 Exit Function 'not found
             end If
         end With
 end Function

if I play my function on the immediate windows the result is immediate but, if I put the function in the cell like = Tmese ("GLS"; 2) nothing happens Why?! ??!

Answer 1

Following up on most of the comments above:

1. You need to make sure Find is successful by using If Not x Is Nothing Then.

2. You can use the WorksheetFunction.EoMonth to find the end of the month of m.

Function Tmese Code

Function Tmese(c As String, m As Integer) As Integer  ' C as Carrier, as m month

    Dim y As Date, x As Variant

    y = WorksheetFunction.EoMonth(CDate("1/" & m & "/2016"), 0) ' find add of the month of m
    With Worksheets(c)
        Set x = .Columns(1).Find(What:=CStr(y))
        If Not x Is Nothing Then
            Tmese = .Cells(x.Row, 5)  ' found max dates into a month to retreive corrispondence fair
        Else
            Exit Function 'not found
        End If
    End With

End Function

GLS worksheet Data

How to apply in another worksheet