CODEWITHSUNDEEP
vbaexcelexcel-formula
Surabhi Anurag
Surabhi Anurag

Reputation: 3

Generating number of Saturdays in VBA

I am new to coding and would like help to write a macros code for generating number of Saturdays for each month when first of each month date is listed already as a range in excel file. The file should look like below

Month   Weeks
1/1/2016    5
2/1/2016    4
3/1/2016    4
4/1/2016    5
5/1/2016    4
6/1/2016    4
7/1/2016    5
8/1/2016    4
9/1/2016    4
10/1/2016   5
11/1/2016   4
12/1/2016   4

Upvotes: 0

Views: 411

Answers (2)

user4039065
user4039065

Reputation:

I'm unclear on why you require VBA for this.

As a worksheet formula,

=NETWORKDAYS.INTL(A2, EOMONTH(A2, 0), "1111101")

Upvotes: 6

Darrell H
Darrell H

Reputation: 1886

This will work. You will need to add Sheets("SheetName") in front of the Cells.

Sub CountSaturdays()

    LR = Cells(Rows.Count, 1).End(xlUp).Row

    For x = 2 To LR
        StDt = Cells(x, 1)
        EndDt = WorksheetFunction.EoMonth(Cells(x, 1), 0)
            For Dt = StDt To EndDt
                If WorksheetFunction.Weekday(Dt) = 7 Then
                    Sat = Sat + 1
                End If
            Next Dt
        Cells(x, 2) = Sat
        Sat = 0
    Next x
End Sub

Upvotes: 1

Related Questions