Referencing Dynamic Ranges in Macro

[zekefish4]

Hello,

I am programming a spreadsheet that automatically populates a timeline based on a number of different factors. One of these factors is which factory will produce the desired product. Each of these factories have different holidays so depending on which factory is selected from a validation menu, the corresponding list of holidays will be used to exclude from the timeline. I was able to get this to work using static ranges for the holidays for each of the factories, however I was hoping that people could add holidays as they came up so I wanted to make the range dynamic.

This is what I have named the dynamic range within the Sheet:

All_FL_Holidays = OFFSET(Sheet2!$Z$2,0,0,MATCH("*",Sheet2!$Z:$Z,-1),1)



Now, my dilemma is that when I try to use this code:


Case "FL"

Range("E21").Value = SixDayWeek(Range("E22"), 10, Range("All_FL_Holidays"))

'(The function SixDayWeek has the syntax: SixDayWeek(StartDate, # of days later, Holidays)


the timeline does not update for different factories, however I do not get any error messages. However, when I change "All_FL_Holidays" to a static list (Z2 : Z20) and I play with timeline, the dates do update. So, I was wondering if I have to use something other than "Range" in the Macro because it seems to me that "Range" only works with static ranges defined in the Sheet. Any help would be greatly appreciated.


-Zeke

[snb]

All_FL_Holidays = OFFSET(Sheet2!$Z$2,0,0,MATCH("*",Sheet2!$Z:$Z,-1),1)

Why do you use Sheet2!$Z$2

and why do you use Sheet2!$Z:$Z

If Z1 is empty it could cause a problem.

[zekefish4]

snb,

I use Sheet2!$Z$2 because I have my dates in the Z column starting in Cell Z2 (I have a title for the column in cell Z1).

I'm not exactly sure why I use Sheet2!$Z:$Z I think I put it in like that after reading about dynamic lists.

But, do you know if the "Range" function in VBA works for dynamic lists?




Thanks again,
Zeke

[snb]

You'd better post your function SixDayWeek here. I can't assess the arguments (whether they are date, integer, range or....)

[zekefish4]

Here is the function:




Public Function SixDayWeek(ByVal IStartDate As Long, ByVal IDays As Long, ByVal rgeHolidays As Range) As Long

Dim Idate As Long
Dim idaycount As Long
Dim bholiday As Boolean
Dim objcell As Range

If IDays > 0 Then idaycount = idaycount + 1
If IDays < 0 Then idaycount = idaycount - 1
Do While (IDays <> 0)
Idate = IStartDate + idaycount
bholiday = False
If WorksheetFunction.Weekday(Idate, 2) < 7 Then
For Each objcell In rgeHolidays
If Idate = CLng(objcell.Value) Then
bholiday = True
End If

Next objcell
If bholiday = False Then
If IDays > 0 Then IDays = IDays - 1
If IDays < 0 Then IDays = IDays + 1
End If
End If
If IDays > 0 Then idaycount = idaycount + 1
If IDays < 0 Then idaycount = idaycount - 1
Loop
SixDayWeek = IStartDate + idaycount


End Function

[snb]

Please Login or Register  to view this content.

[zekefish4]

snb,

I tried using that code for the SixDayWeek function, however it did not work correctly. There were no error messages but the calculations were drastically off. I feel like that might not be the issue, but simply if the "Range" property in VBA will allow for dynamic ranges to be used, or if I should define the dynamic ranges in VBA if possible (and how to do that). Thanks for all of your help.

-Zeke

[snb]

Please Login or Register  to view this content.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[zekefish4]

Sorry about the non-compliance with the code, but snb, thank you for all of your help and I was able to get the code to work as I had planned.

I changed the dynamic range from:

All_FL_Holidays = OFFSET(Sheet2!$Z$2,0,0,MATCH("*",Sheet2!$Z:$Z,-1),1)

to

All_FL_Holidays = OFFSET(Sheet2!$Z$2,0,0,MATCH(9999999,Sheet2!$Z:$Z,1),1)



Thanks again for all of your help!

-Zeke