No of weeks (Wednesdays)in month

Hi,

The following formula:

=4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))

correctly calculates the number of Wednesdays in a month but I am having some difficulty in getting this to work in VBA. When I put this into VBA the results are not the same so obviously I cannot just paste this formula in as code. I am still learning Excel vba so hope that this will be an easy one for someone more experienced than me.

This is my code so far:
--------------------------------------------------------------

Private Sub SetupPlanner()
    Dim i As Integer
    Dim CurrMnth As Date
    Dim NoOfWeeks As Integer
    Dim StartDate As Date
    Dim EndDate As Date
    Dim ProjMnths As Integer
    
    StartDate = Worksheets("Sheet2").Range("B5:B5").Value
    EndDate = Worksheets("Sheet2").Range("B6:B6").Value
    ProjMnths = DateDiff("m", StartDate, EndDate)
    
    For i = 0 To ProjMnths
        StartDate = Worksheets("Sheet2").Range("B5:B5").Value
        CurrMnth = DateAdd("m", i, StartDate)

        NoOfWeeks = 4 + (Day(CurrMnth - Day(CurrMnth) + 35) < Weekday(CurrMnth - Day(CurrMnth) - 4))

        Debug.Print CurrMnth, NoOfWeeks
    '    Debug.Print NoOfWeeks
    Next i
    
End Sub

---------------------------------------------------------------------------------

In essence I'm trying to create a spreadsheet for each of our projects which has a start date and and estimated completion date. I will then loop through each of the months for the duration of the project, calculate the no. of weeks in each month and then set up each week as a column in the spreadsheet. However I am stuck in the early stages of the process.

Thanking you in advance for any assistance/advice.

Gerry

Can I just ask in what way do you mean the results are different?

When I put this into VBA the results are not the same so obviously I cannot just paste this formula in as code

I have just inserted the formula manually in Column D and used the macro below to fill column E (see the table formula at the bottom of this post) and the results were the same.

Sub TEST5()
Range("E3").Formula = "=4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))"
    Range("E3").AutoFill Destination:=Range("E3:E10"), Type:=xlFillDefault
End Sub

Cell Formula
D3 =4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))
E3 =4+(DAY(B4-DAY(B4)+35)<WEEKDAY(B4-DAY(B4)+4))
D4 =4+(DAY(B5-DAY(B5)+35)<WEEKDAY(B5-DAY(B5)+4))
E4 =4+(DAY(B5-DAY(B5)+35)<WEEKDAY(B5-DAY(B5)+4))
D5 =4+(DAY(B6-DAY(B6)+35)<WEEKDAY(B6-DAY(B6)+4))
E5 =4+(DAY(B6-DAY(B6)+35)<WEEKDAY(B6-DAY(B6)+4))
D6 =4+(DAY(B7-DAY(B7)+35)<WEEKDAY(B7-DAY(B7)+4))
E6 =4+(DAY(B7-DAY(B7)+35)<WEEKDAY(B7-DAY(B7)+4))
D7 =4+(DAY(B8-DAY(B8)+35)<WEEKDAY(B8-DAY(B8)+4))
E7 =4+(DAY(B8-DAY(B8)+35)<WEEKDAY(B8-DAY(B8)+4))
D8 =4+(DAY(B9-DAY(B9)+35)<WEEKDAY(B9-DAY(B9)+4))
E8 =4+(DAY(B9-DAY(B9)+35)<WEEKDAY(B9-DAY(B9)+4))
D9 =4+(DAY(B10-DAY(B10)+35)<WEEKDAY(B10-DAY(B10)+4))
E9 =4+(DAY(B10-DAY(B10)+35)<WEEKDAY(B10-DAY(B10)+4))
D10 =4+(DAY(B11-DAY(B11)+35)<WEEKDAY(B11-DAY(B11)+4))
E10 =4+(DAY(B11-DAY(B11)+35)<WEEKDAY(B11-DAY(B11)+4))

Hi GerryA,

Sorry coming in a litte late, but this sounds confusing. Your thread title has both no of weeks and Wednesdays so which is it?

Also, you say you have a start date and competion date, for no of weeks, why not, (enddate-startdate) / 7

I don't see the need for VBA and in post #4 not quite understanding all the dates.

Can you maybe attach a sample workbook if that will help to explain it better? What you have and what you desire?