Results 1 to 9 of 9

Thread: List of Dates & Dynamic Name Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default List of Dates & Dynamic Name Range

    Good Afternoon,

    I currently have a list of dates starting in cell A2 which lists every day of the current year (1/1 - 12/31). I also have some named ranges that reference columns A:U for each month (Ex: January is A2:U32, February is A33:U63 etc) . I'm running into an issue where since next year is a leap year (2020), the named ranges are adjusted and throwing the named ranges off by a day.

    Is there a way I can modify these named ranges dynamically so that they automatically adjust based on the date in column A?

    Thanks in advance for any help you can provide!

    JB

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    How are you named ranges being set?

    Are you re-using this each year, and if so, what are you doing when you reset for each year?

    Are you simply clearing the entries (but not deleting the rows), so the named ranges still hold their places?
    If so, what you will need to do is choose some date in the middle of February and insert a row there, and then the set named range for February will grow by one year.
    Then, the following year, you will need to remember to delete a row in February.

    Otherwise, you may want to create a macro that populates the dates and named ranges automatically each year.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    To set the named ranges I go to Name Manager and set the ranges for the specific months (Ex: January is A2:U32).

    To reset each year I am just modifying those named ranges but it's a tedious exercise.

    I already have a macro that populates the dates, but not sure how to populate the named ranges.

    I was hoping it would just be as simple as creating a dynamic named range somehow that if the date in column A is January, select columns A:U for those dates, etc.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    If you clear the data entries (and not actually delete the rows) each year, the named ranges would stay intact.
    Then you wouldn't need to reset them every year. However, the leap year would still provide a challenge.
    But as long as you already have a macro, you can use the macro to add the named ranges too.

    However, before we do that, can you tell me what you are using the named ranges for?
    They may actually not be necessary at all, depending on what you are using them for.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Sep 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    It's a pretty robust workbook and I'm not the best at explaining but I will give it a shot. Basically the named ranges are copied to another sheet based on user input on which specific month(s) are needed which is then exported to a new workbook as a data file and then imported to another workbook.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    OK, I was thinking you might be able to just use filters to pull the data that you need, but we can stick with what you what you are doing now.
    So let's just try to build off what you have now.
    Can you post the VBA code that you currently have to populate the dates?

    And for your named ranges, are you just including column A or other columns in addition to it?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Sep 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    There's a lot of code that copies the dates but here's what populates the dates for January. This code repeats again for every following month for the year and it may seem a little complex but I'll explain what it does. Column AB has 4 values that repeat with every month in column D. Column AC has a list of months formatted as "mmm" dates. Column AD is a formula that calculates the number of days in the months in column AC.

    Option ExplicitSub Macro1_CopyText_n_Dates()


    Dim wsOutputTab As Worksheet
    Dim bteNumOfDays As Byte
    Dim rngMyCell As Range
    Dim blnFirstText As Boolean
    Dim i As Byte


    Set wsOutputTab = Sheets("Forecast Data")

    Application.ScreenUpdating = False

    'January

    For Each rngMyCell In wsOutputTab.Range("AC2")
    If MonthName(Month(rngMyCell), True) = MonthName(Month(wsOutputTab.Range("AC2")), True) Then
    bteNumOfDays = rngMyCell.Offset(0, 1)
    Exit For
    End If
    Next rngMyCell
    If bteNumOfDays = 0 Then
    MsgBox "There was an error trying to assign the number of days for the month." & vbNewLine & "Please check and try again.", vbExclamation
    Exit Sub
    End If




    blnFirstText = True

    For Each rngMyCell In wsOutputTab.Range("AB2:AB" & wsOutputTab.Range("AB" & Rows.Count).End(xlUp).Row)
    For i = 1 To bteNumOfDays
    If blnFirstText = True Then
    wsOutputTab.Cells(2, "A") = wsOutputTab.Range("AC2") + i - 1
    wsOutputTab.Cells(2, "D") = rngMyCell.Value
    blnFirstText = False
    Else
    wsOutputTab.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = wsOutputTab.Range("AC2") + i - 1
    wsOutputTab.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = rngMyCell.Value
    End If
    Next i
    Next rngMyCell

  8. #8
    New Member
    Join Date
    Sep 2017
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    Also, the named ranges are from columns A:U... For example January is A2:U32

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: List of Dates & Dynamic Name Range

    OK, so it looks like you are starting on row 2, "rngMyCell" contains a date in the month, and "bteNumDays" holds the number of days in the month.
    Then, you could use that information to set the named range like this:
    Code:
        Dim rngName As String
        
        rngName = Format(rngMyCell, "mmmm")
        
        ActiveWorkbook.Names.Add Name:=rngName, RefersToR1C1:= _
            "=Sheet1!R2C1:R" & bteNumDays + 1 & "C21"
    Note that you will need to change the part in red to match whatever sheet you are putting this named range on.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •