You can use the **EoMonth **method in VBA to return the last day of the month for a given date.

Here is one common way to use this method in practice:

**Sub LastDayOfMonth()
Dim i As Integer
For i = 2 To 11
Range("C" & i).Value = Application.WorksheetFunction.EoMonth(Range("A" & i), 0)
Range("C" & i).NumberFormat = "m/d/yyyy"
Next i
End Sub
**

This particular macro finds the last day of the month for each date in the range **A2:A11** and displays this date in the corresponding cell in the range **C2:C11**.

The following example shows how to use this syntax in practice.

**Example: How to Use EoMonth in VBA**

Suppose we have the following dataset in Excel that contains information about the sales made by some company on various dates:

Suppose we would like to find the last day of the month for each date in column A and display it in column C.

We can create the following macro to do so:

**Sub LastDayOfMonth()
Dim i As Integer
For i = 2 To 11
Range("C" & i).Value = Application.WorksheetFunction.EoMonth(Range("A" & i), 0)
Range("C" & i).NumberFormat = "m/d/yyyy"
Next i
End Sub**

When we run this macro, we receive the following output:

Notice that column C contains the last day of the month for each corresponding date in column A.

Note that we used the **NumberFormat** property to display the date values in a recognizable date format in column C instead of serial numbers, which is the default format in Excel.

Also note that the last argument of the **EoMonth** method specifies how many months ahead or behind to use when calculating the last day of the month.

In this example we used a value of **0** to specify that we’d like to find the last day of the current month.

However, we could instead use a value of **-1** to specify that we’d like to find the last day of the previous month or a value of **1** to specify that we’d like to find the last day of the next month.

**Note**: You can find the complete documentation for the **EoMonth** method in VBA here.

**Additional Resources**

The following tutorials explain how to perform other common tasks in VBA:

How to Compare Dates in VBA

How to Convert String to Date in VBA

How to Calculate Days Between Two Dates in VBA