Code using WRONG COLUMN to group invoices by date and totals in I should be using Col F

I'm having a problem with this code- picking up the wrong column! -- I tried to adjust some numbers - but didn't get the desired result..
(see attachment)
This code goes through a table of Invoices and generates a new sheet (shown in attch'd) to place those invoices in a GROUPED/Segmented way by week.
Hoping someone can see where the adjustment needs to take place so it will use the DUE DATE (column E) as it's source when placing into segments.
Currently it appears to be using (column D) which really jacks up the segmented report..

The weeks run MONDAY through SUN -- therefore a DUE DATE of 6th would be placed in the first segment, 13th would be in 2nd segment, etc.
For example, Jan of 2018's weeks would be:
1st-7th
8th-14th
15th-21st
22nd-28th
29th-4th of Feb

Tried changing these d's to e's? but didn't work -- appears other references need to be adjusted---

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Column I's totals SHOULD BE USING Column F when it goes to place the SUM out at right --
Not sure if that's working properly -- seemed to be off

Thanks greatly!

lol -- that's what I need help with -- I don't know!? or I'm not understanding your question...
From the results of the image posted -- the code is using the wrong column -- it is using Col D to group dates.. and SHOULD BE USING Col E
(notice how rows 48-51 of COL E have mixed dates and are clearly not all from the same WEEK)
So, I need the code to use E
In other words, Col E should have the 01/03/17 invoice in a segment by itself because none of the other Col E invoice DUE dates fall within that week of 1/1-1/7.
Next, the 2 invoices with DUE DATES of 01/13 would be grouped in the Jan 8-13th segment
Next, the 2 invoices with DUE DATES of 01/18 would be grouped in the Jan 15-21 segment
Next the 2 invoices with DUE DATES of 02/28 would be grouped in the Jan 22-28 segment
Next, the 2 invoices with DUE DATES of 02/20 would be grouped in the Feb 19-25 segment
Next, the 1 invoice with DUE DATE of 02/26 would be alone in the Feb 26-Mar 4th segment
Last, the 1 invoice with DUE DATE of 03/05 would be alone in the Mar 5-11 segment

(there's a lot more invoices so, this grouping is helpful to see what was DUE each week and how much income was supposed to come in that week -- vs the actual --- as well as a look-ahead of what's income is scheduled to come in on future weekly segments on various jobs...)

yes, I'll get one stripped out and get it posted shortly =-)

Here's a sample file with all the code crammed into a single Module -- that takes the original large, large report and generates the tabs you see. The first weekly segmented report is the one that was generated with the code (that has the issue) --
The invoices should be grouped by DUE DATES but the code is grouping them using the other date column..

Try change to

Please Login or Register  to view this content.

Your original thread
https://www.excelforum.com/excel-pro...p-by-date.html

Originally Posted by CHRISOK

(all starting on Mondays and Ending on Sundays)

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

awesome! thanks for link -- I had a diff issue there but it still included this code.

Now, for the grouping of dates - still getting the issue even after changing it to Tuesday... Here's the outcome this time..
(I pasted the date ranges table out to right to verify what's in the wrong group - the pink ones are including ones that should not be there)

I'll start changing it to each day of the week and running it to see if I can find one that works -- if you see another way - let me know - I'll let you know if any other day works.. starting with Monday..

Here's outcome when that same setting was changed to Monday (still problems in pink)

So we've used: Sunday, Tuesday, now Monday -- I'll keep trying the other days to see what outcomes might be...

Is it a same workbook that you have posted?

No, the one you just posted has a tab wiped out (although the name is the same as the one I originally posted). The one I posted has also has 3 tabs but the Wkly Totals tab is not wiped out.
The Wkly Totals tab is the one that gets generated after running all the Modules in order..

I'm using the original for testing because it has the original Sheet1 report in it that gets stripped and eliminated.. the leftovers from it end up on the STRIPPED-DOWN-MASTER tab ... and from THAT tab, the final tab called: Wkly Totals is generated (where it should be taking what it sees in the STRIPPED DOWN MASTER tab and placing the rows into a Wkly segmented look..

Newly attached is the same original file I posted above (but I've added back in the "Sheet1" tab that gets eliminated when the code is run.

In the orig file, I threw all 4 MODULES into a single module (now named "OLD").
To test it from scratch, DELETE the Wkly Totals tab and STRIPPED MASTER tab -- then start with MOD 1, MOD2, MOD 3 then MOD 4
Then go to the newly generated Wkly Totals tab and see the way things got segmented..

I just tried using "SATURDAY" as a replacement and see it's still having grouping by Due Date issues.
So far, Sat, Sun, Mon & Tues have been tried -- I'll continue with replacing with Wed, Th, & Fri..

Originally Posted by jindon

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

You didn't follow this change.

Yes, I did -- I've tried changing it to Tues, Wed, Thur, Saturday, Sunday (originally) and FINALLY just now tried FRIDAY and FRIDAY SEEMS TO BE THE WINNER!
Yeah!! not sure why FRIDAY works -- but I'm just happy it works! I checked every one all the way up and all were placed into the correct weekly segment!
Here's the results of the TUES (with pink issues) vs FRI (with all yellow confirmed each is accurate!)

THANK YOU SO MUCH FOR YOUR HELP -- getting me to the place that needed tweaking! (I'll click the scales icon to add this success to your REPUTATION)

Originally Posted by CHRISOK

Yes, I did -- I've tried changing it to Tues, Wed, Thur, Saturday, Sunday (originally) and FINALLY just now tried FRIDAY and FRIDAY SEEMS TO BE THE WINNER!

That's hard to believe..., but if it works for you that will be OK.

Yes, I know -- it made no sense -- but I'll let you know if things start to look wonky when I run next week's report... Thx again!

BACK after testing and sad to report the problem is still present...
Attached is the sample file that has 2 modules (Module 2 is where the problem lies).
It is not breaking the rows out into the proper weekly segments. (example of what is happening is highlighted w/ notes on each tab)
Hoping someone can figure out why it's not generating the right groupings?

There's a tab that shows what each start date and end date should be for every week --
I included it to visually see what those weeks SHOULD BE and also in case it might be helpful to have code use it to perform it's break outs..? If not, ignore it.

Thanks in advance for any help getting it working properly...

Try change to

Please Login or Register  to view this content.

You are welcome and thanks for the rep again.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.