Assign Month to the Date Range (via VBA)

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".
Who is Participating?
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
Ryan ChongCommented:
can you provide a sample with your existing data and expected result?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

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)
CABRLU63Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.