Data parsing issue

[p4k786]

Basic overview:
I parse the first column and for every date, I convert it to Wednesday's date and store it elsewhere (currently in the same sheet). So if the date being checked falls on a Wednesday, it is stored, otherwise the date of next Wednesday is stored. There is a value under each date so I add it to the total (initial is 0). For the next date in the column, if the converted Wednesday's date is same as previously stored, then I just update the number value. When a different Wednesday's date appears, I store the current number total next to the week's date, reset, and repeat the process. I hope it makes sense.

Example (like in workbook):
8/20/2015
1
8/21/2015
2
8/23/2015
1
8/24/2015
1
8/25/2015
1
8/26/2015
1
8/31/2015
1
9/1/2015
5
9/3/2015
3

First date will yield 8/26/2015 as the Wednesday's date. Store the number value 1 (in a variable or cell). The next 5 dates (up to and including 8/26/2015) correspond to Wednesday dating 8/26 so we add up all the number values, so now our week total is 7. Next is 8/31 which falls under 9/2/2015 week, so you reset the total and repeat.

What I want to get:
8/26/2015 7
9/2/2015 6
9/9/2015 3

What I currently get:
8/26/2015 1
9/2/2015 7
9/9/2015 8

I probably need to set an initial date which is same as the first date, but that did not work for me either.
It wouldn't let me attach a file, so I am uploading the file and providing the link (I hope I am allowed to do so): https://mega.nz/#!DwoBXQ7J!LrC_AgP6k3PUNJmbQr2PCphcig5g76aG_TqEE29ixeU

Let me know if I am not allowed to do that, here is the code also:

Sub TP_Analysis()
    Dim myBook As Workbook: Set myBook = ThisWorkbook
    Dim mySheet As Worksheet: Set mySheet = myBook.Sheets("TP_Test")
    mySheet.Activate


    Dim totRows As Integer
    totRows = mySheet.Cells.SpecialCells(xlLastCell).Row


    Dim currRow
    Dim startRow As Integer: startRow = 35
    Dim dataRow As Integer: dataRow = startRow


    Dim tempCell: tempCell = "G33"
    Dim prevDate


    ' Set prevDate to first date's Wednesday (to skip over If Not loop the first time)
    'Range("G33").Value = "=A" & startRow & "+MOD(4-WEEKDAY(A" & startRow & "),7)"
    'prevDate = Range("G33").Value
    For currRow = startRow To totRows
        Dim tpDate: tpDate = Range("A" & currRow).Value
        If IsDate(tpDate) Then
            Range(tempCell).Value = "=A" & currRow & "+MOD(4-WEEKDAY(A" & currRow & "),7)"
            Range(tempCell).NumberFormat = "m/d/yyyy"
            Dim tempDate: tempDate = Range(tempCell).Value


            totTP = totTP + Range("A" & currRow + 1).Value


            If Not (prevDate - tempDate) = 0 Then       ' If not same date
                Range("G" & dataRow).Value = tempDate
                Range("G" & dataRow).NumberFormat = "m/d/yyyy"
                Range("H" & dataRow).Value = totTP
                prevDate = tempDate
                dataRow = dataRow + 1
                totTP = 0
            End If
        End If
    Next currRow
    Range(tempCell).Clear
End Sub

Any help towards solving the issue and/or suggestions to make the code more efficient will be appreciated!

 

[Eric W]

Welcome to the MrExcel forum!

You can do all that with formulas without any VBA code.

G33: =MIN(IF($A$33:$A$1000<10000,99999,$A$33:$A$1000+7-WEEKDAY($A$33:$A$1000,14)))
Confirm with Control-Shift-Enter

G34: =IFERROR(SMALL($A$33:$A$1000+7-WEEKDAY($A$33:$A$1000,14),SUM(IF($A$33:$A$1000+7-WEEKDAY($A$33:$A$1000,14)<=G33,1,0))+1),"")
Confirm with Control-Shift-Enter, then copy it down the G column as far as necessary.

H33: =SUM(IF($A$33:$A$1000+7-WEEKDAY($A$33:$A$1000,14)=G33,$A$34:$A$1001,0))
Confirm with Control-Shift-Enter, then copy it down the H column as far as necessary.

Let me know how that works. If you have some reason for preferring VBA, let me know and I'll take a look at your code.