VBA Help - Using Date Range to drop in Values

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
Hello All,

Working on a project and I have no idea how I would achieve something like this so I am hoping someone might have some ideas.

Variables: I have 5 fields of criteria that drive how the sheet will display data
1. My Title
2. A Start Date
3. An End Date
4. A First Drop Count - This essentially tells the user that based on the #2 Start Date how much activity happens
5. Activity Count - Just a total of how much activity there is. This should match the amount of weeks in between the Start and End date (The dates are formatted by week using Friday's Date for each of those weeks)

So, I have mocked up a sample of what the sheet would look like in the event I was able to get this to work.

So, based on the first row of data "Title 1", my start date begins on 11/1/19 and has a First Drop of 3, I would need the corresponding cell in this row to show "1-3", if the First Drop is "1" to display just "1" and since my End Date is 12/20/19 I would need each subsequent cell after the Start Date cell to continue the Counter "2", "3" and so on until it reaches the end date and then one cell to the right of the end date to drop in the title in that cell.

I would most likely need some sort of Validation to look at the start date and end date and get a count of how many weeks are in between and then confirm if the "Activity Count" value matched this number or else flag the user via a msgbox and then exit the macro to allow for a correction.

If any other details are needed please let me know.

Sample
TitleSeries StartSeries EndFirst DropActivity Count4111825181522296132027
Dates >>>>10/4/1910/11/1910/18/1910/25/1911/1/1911/8/1911/15/1911/22/1911/29/1912/6/1912/13/1912/20/1912/27/19
Title 111/1/1912/20/193101-345678910Title 1
Title 210/25/1912/13/193101-345678910Title 2
Title 310/11/193/13/2018145678Title 3
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Here's a brain-bending formula for you

=IF(OR(G$2<$B3,G$2>$C3),IF($D3+(G$2-$B3)/7=$E3+1,$A3,""),IF($B3=G$2,"1"&IF($D3>1,"-"&$D3,""),IF($D3+(G$2-$B3)/7<=$E3,$D3+(G$2-$B3)/7,IF($D3+(G$2-$B3)/7=$E3+1,$A3,""))))

This is the formula for Title 1 Week 10/4/19 (if Title 1 is row 3 and 10/4/19 is column G). Just copy this formula into the other cells.

Two things I don't understand
1. The Activity Count for Title 3 doesn't match the number of weeks. Is this a deliberate error? You mentioned some sort of validation check. I would suggest conditional formatting (say red text) to indicate a mismatch between the Activity Count and the number of weeks.
2. For Title 3, why is the second week 10/18/19 4? The First Drop is 1 so I would have expected it to be 2. That is what my formula shows.

If you need a more detailed explanation of what the formula is doing, post a reply.
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
You were totally correct, I had some errors on the sheet but they were not intentional so my bad. The formula you provided worked spot on. And yes, this was a brain bending formula. Thank you for the suggestion! This worked great!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,992
Messages
5,628,014
Members
416,286
Latest member
ko15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top