Excel VBA dynamic named ranges

Hello,

Is it possible to have code that looks in cell B3 to find the day, month and year and then changes the references ranges of each named range to select only days that are Monday, Tuesday, Wednesday, Thursday or Friday for that month.

I know I could set the ranges for each month and then use an if statement on the B3 cell but is there an easier way?

Thank you.
Sq30
Testfile.xlsm
sq30Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PAGANEDCommented:
I am thinking initially that i would have a formula based column next to my date that I would calculate the DAY based on the day Number (1-7).  Then I could use that for determining my range select to the associated Date column next to it
sq30Author Commented:
I already tried that using weekday but you have to take the month into account also the the column will always contain 31 days but as some months are 30 or 28/29 days long the last few cells could contain M-F of the following month which I would no want in my ranges.
Martin LissOlder than dirtCommented:
It also looks like you are planning on having multiple named ranges for columns B to U. Why not just have one for B and use Offset for the others?
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Martin LissOlder than dirtCommented:
What is the purpose of the named range? Is it just to color the cells? If so you could probably use Conditional Formatting.
PAGANEDCommented:
Do you want this to be VBA code that you have run on a specific cell value change ?
.
in Excel 2010 there is the formula:  Weekday()  that gives you the 1-7
.
I do not think you are asking for something this simple, though
sq30Author Commented:
Pagned -

Do you want this to be VBA code that you have run on a specific cell value change ? No I will set the code off myself.

In Excel 2010 there is the formula:  Weekday()  that gives you the 1-7 - Yes I already mentioned that I had tried that.
sq30Author Commented:
Martin Liss

Each cell will contain data and the purpose of each named range is to make other formulas easer when referencing these cells. Is it possible to offset when naming ranges?
Martin LissOlder than dirtCommented:
To color the cells based on B3 add Conditional Formatting as follows:

Select the range you want to color
Home tab|Conditional Formatting
New Rule
Use a formula to determine which cells to format
In 'Format values where this formula is true:" enter =WEEKDAY($B3,2)<6
Click 'Format...'
Select color and click 'OK' twice
sq30Author Commented:
I don't want to colour the cells that was only to make my example easier to see how the day's shift each month.
Martin LissOlder than dirtCommented:
Is it possible to offset when naming ranges?
I'm not sure I'm answering your question but in VBA using your Range1 named range

'To affect all cells in column 'F' 
Range("Range1").Offset(0, 4) = "Hi"

'To change G3
Range("Range1").Cells(1, 1).Offset(0, 5) = "There"

Open in new window

sq30Author Commented:
Martin - No but your putting me on the right track

ActiveWorkbook.Names.Add Name:="Range2", RefersTo:=Range("Range1").Offset(0, 10)

Open in new window


Will allow me duplicate the ranges for each named range offset by on each time
Martin LissOlder than dirtCommented:
OK. What else do you need?
sq30Author Commented:
How to automatically change the cell ranges stored within named "range 1" for each month.
Martin LissOlder than dirtCommented:
This macro will change the named range
Sub ChangeNamedRange()
Dim NR As Name
Dim lngRow As Long
Dim rng As Range

Set NR = Names.Item("Range1")

Sheets("Data").Activate
For lngRow = 3 To 33
    If Weekday(Cells(lngRow, 1)) > 1 And Weekday(Cells(lngRow, 1)) < 7 Then
        If rng Is Nothing Then
            Set rng = Cells(lngRow, 1)
        Else
            Set rng = Application.Union(rng, Cells(lngRow, 1))
        End If
    End If
Next

NR.RefersTo = rng
End Sub

Open in new window


And if you add this code to the Data worksheet it will run automatically.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" Then
        ChangeNamedRange
    End If
End Sub

Open in new window

sq30Author Commented:
Thanks Martin - nearly there, I think month needs adding in also as if you change B3 to June 1st cell B33 is being picked up as part of the range when it's July 1st and should not be included.
Martin LissOlder than dirtCommented:
I was mistakenly looking at column 'A' rather than 'B' so try this instead.

Sub ChangeNamedRange()
Dim NR As Name
Dim lngRow As Long
Dim rng As Range

Set NR = Names.Item("Range1")

Sheets("Data").Activate
For lngRow = 3 To 33
    If Weekday(Cells(lngRow, 2)) > 1 And Weekday(Cells(lngRow, 2)) < 7 Then
        If rng Is Nothing Then
            Set rng = Cells(lngRow, 2)
        Else
            Set rng = Application.Union(rng, Cells(lngRow, 2))
        End If
    End If
Next

NR.RefersTo = rng
End Sub

Open in new window

Martin LissOlder than dirtCommented:
To have the named range refer only to cells of the month referred to in B3 then

Sub ChangeNamedRange()
Dim NR As Name
Dim lngRow As Long
Dim rng As Range

Set NR = Names.Item("Range1")

Sheets("Data").Activate
For lngRow = 3 To 33
    If Month(Cells(lngRow, 2)) = Month(Range("B3")) Then
        If Weekday(Cells(lngRow, 2)) > 1 And Weekday(Cells(lngRow, 2)) < 7 Then
            If rng Is Nothing Then
                Set rng = Cells(lngRow, 2)
            Else
                Set rng = Application.Union(rng, Cells(lngRow, 2))
            End If
        End If
    End If
Next

NR.RefersTo = rng
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sq30Author Commented:
It's still picking up any day's going into the next month e.g if you enter Feb 1st cells B32/33 are picked up also in the range but they belong to March. I've changed one line of you code to;

If Weekday(Cells(lngRow, 2)) > 1 And Weekday(Cells(lngRow, 2)) < 7 And Month(Cells(lngRow, 2)) = Month(Range("data!B3")) Then

Open in new window


and it works perfect :D

Many thanks for your help.
Martin LissOlder than dirtCommented:
You're welcome and if that works for you then great but using the code as is in post 40779786 results in the following. Note that the range as indicated by the "marching ants" only includes February dates.
No March dates included
sq30Author Commented:
Sorry - I hadn't refreshed before adding comment 40779787 so ended up with a sort of cross post. I tweaked 40779771 to get the result I was after and your absolutely right 40779786 works perfect to. Thanks again for your help, much appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.