Assign Month to the Date Range (via VBA)

Posted on 2016-10-20
Last Modified: 2016-10-24
I have Excel tab with 8 dates on it (in 8 columns).
Say, in columns 13 thru 20.
Between Date from Column 13 and the Date from Column 14 there is a RangeA.
Meaning that for example Column 13 value is 1/1/2016 and Column 14 value is 4/15/2016.
So date 1/1/2016-4/15/2016 is my RangeA.
On the other Tab I have 20 columns with Months, including those above: Jan 2016 and April 2016.
I need to tie them together, meaning:
If there is RangeA in that first Tab, then on my other Tab I need to Populate that corresponding Cell(s). In our case cells Jan 2016, Feb 2016, March 2016 and April 2016 should be populated with "RangeA".
Question by:CABRLU63
LVL 50

Expert Comment

by:Ryan Chong
ID: 41853531
can you provide a sample with your existing data and expected result?
LVL 19
ID: 41853661
Public Function GetTheDateRange( _
   nCol1_1 As Long _
   , nCol1_2 As Long _
   , nRow1 As Long _
   , nRow2 As Long _
   ) As String
   '  nCol1_1 = column with first date to look through (RangeSheet )
   '  nCol1_1 = column with second date to look through (RangeSheet )
   '  nRow1 = row with dates to look through (RangeSheet )
   '  nRow2 = row with date on sheet with formula to look at (sheet with formula)

   '  =GetTheDateRange(13,20,1,1) --> 1/1/2016 - 4/15/2016
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet

   Set ws1 = Worksheets("RangeSheet") 'RangeSheet = sheet name with dates to look through -- CUSTOMIZE
   Set ws2 = Application.Caller.Parent 'worksheet that the formula is on

   Dim nDate1 As Date _
      , nDate2 As Date _
      , nCol As Long _
      , nDate As Date _

   'initialize return value
   GetTheDateRange = ""
   nDate = CDate("1 " & ws2.Cells(nRow2, Application.Caller.Column))
   With ws1
      For nCol = nCol1_1 To nCol1_2
         If .Cells(nRow1, nCol) <= nDate Then
            If .Cells(nRow1, nCol + 1) >= nDate Then
               GetTheDateRange = "'" & .Cells(nRow1, nCol) & " - " & .Cells(nRow1, nCol + 1)
               Exit Function
            End If
         End If
      Next nCol
   End With
   On Error Resume Next
   Set ws1 = Nothing
   Set ws2 = Nothing
   Exit Function
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetTheDateRange "

   Resume Proc_Exit
End Function

Open in new window

LVL 18

Expert Comment

ID: 41853669
You can create a 3rd sheet and concatenate the two or if it simpler than that meaning that you have a sheet with your range A with dates like 15/1/2016 and 4/15/2016 and then you want on a second sheet to show just the month and the year, you can use a reference to the initial sheet and format the date (like the example I have attached)
LVL 19

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41853687
I actually didn't use nDate1 or nDate2 so they can be removed from the DIM statements. Tried to edit but xtermie was too fast ;)

also changed function result to:
               GetTheDateRange = CStr(.Cells(nRow1, nCol)) & "-" _
                  & CStr(.Cells(nRow1, nCol + 1))

here is an example of what the output looks like:
Excel - screenshot showing GetDateRange function(my dates aren't in order like yours probably are)

Like Ryan, I didn't really understand what you were after so I took a guess

Author Comment

ID: 41853864
The question I've asked will help me to get started with the Gantt Chart project I'm working on.
It has to be a VBA code.
I'll have a tab with RawData (the attached ActiveProjects file) and that data will feed the the Gantt Chart (as the one on GanttCapture file).
I've already built some code to take data from the external file and populate first 4 columns with project details. Now need to start actually populating Gantt.
This is also my objective here:
The code will need to define Years (consecutive), Quarters (4 for each Year) and Months (3 for each Quarter).
Months of the Quarters (for example 1Q - 1QJan, 1QFeb, 1QMarch).
It will also have to define when the Start and End of each Quarter is. Also, tie each Quarter to the Year.
Date Ranges for Stages. For example: GREEN Stage – that is DesignStart to DesingEnd date range. YELLOW Stage – is DesingEnd to ConstructionNTP. ORANGE Stage – is from ConstructionNTP to FinalCompletion. And also BLUE Stage – is from FinalCompletion Date plus 11 Months forward.
Stages (GREEN, YELLOW, ORANGE and BLUE) defined and tied to Dates from the RawData tab (example: DesignStart_To_DesignEnd Date is GREEN) and then these Stages tied to the above Quarters.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question