Filtering Data based on previously filtered data selection

[Cooliozar]

Hello, I'm attempting to put together a Holiday Staffing Planner the returns certain values and hourly counts based on data input and filtering from other worksheets. My workbook has the following worksheets:

* Holiday Staffing Planner - (User Input Worksheet)
* DD_VALUES - Data Validation for reference on the Holiday Staffing Planner Worksheet for drop down values
* CALENDAR - Worksheet that automatically updates all Holidays to the specified Year from the Holiday Staffing Planner worksheet. It also updates and displays the day of week in a separate column
* DATA - Worksheet that uses Microsoft Query to import data from an exteral source to include APLN_DT (M/DD/YYYY), APLN_DT_TM (M/DD/YYYY mm:ss), DLR_REGION (TEXT),
APP_HR_CD (0-24), APP_HR (1 AM)


On the Holiday Staffing Planner I have a cell to select the Region, Holiday, Starting Year, and Ending year from a Drop Down. C5:C8 respectively. I have D5:D8 reserved for Date and E5:E8 reserved for weekday.

I need to get my code to first filter the CALENDAR worksheet for the input value from C:6 and return both of the found values from CALENDAR BX and CX into Holiday Staffing Planner D6 and E6.
Once the code has the value for D6 Date (M/DD/YYYY) then I need the code to do a second filter on the DATA worksheet based on the Date value from D6.


This is the first part since it should give me a range based on the Date selected.


Once the DATA worksheet has been filtered by Date, as specified, I now need to have my code return a number count based on each APP_HR_CD and return it to the Holiday Staffing Planner Worksheet for each hour as something like this:

7:00AM 8
8:00AM 12
9:00AM 15
10:00AM 18
11:00AM 22
12:00PM 26
1:00PM 33
2:00PM 35
3:00PM 30


Where the Date select has a row for APP_HR_CD and APP_HR_CD 0-24 represents each hour of time for that date and returns it to the relative Time slot next to the hour as set on the Holiday Staffing Planner worksheet in C12:C35 where B12 = 1:00 and B35 = 0:00.


My apologies if this is confusing. It seemed like an easy ask when I first began. I didn't want to post with an error that I'm getting becuase I'm getting several every time I attempt to change something and run the code.


My current code:


Code:
Sub HolidayStaffing()

Dim sRegion As String, sHoliday As String, dDate As Date
Dim RngAry(4) As Range, CritAry(2) As Variant, vReturn(31) As Double
Dim InAry(1 To 4) As Variant
Dim i As Long, j As Long
Dim Rngadd As Variant, ColAry1 As Variant, ColAry2 As Variant


ColAry1 = Array(2, 3)
ColAry2 = Array(1, 3, 4, 5)
Rngadd = Array("Calendar", "A1:C12", "DATA", Worksheets("DATA").ListObjects("Table_Query_from_DW7PRD_1"))


With Application
.EnableEvents = False
.ScreenUpdating = False
End With


For i = 3 To ActiveWorkbook.Worksheets.Count
If (Sheets(i).AutoFilterMode And Sheets(i).FilterMode) Or Sheets(i).FilterMode Then
Sheets(i).ShowAllData
End If
Next i


'InAry(1) - Region
'InAry(2) - Holiday
'InAry(3) - Year (Starting)
'InAry(4) - Year (Ending)


For i = 1 To 4
InAry(i) = Sheets("Holiday Staffing Planner").Range("C" & i + 4).Value
Next i

'Range 1
CritAry(0) = Array(1, InAry(2))


For i = 0 To UBound(Rngadd) Step 2
Set RngAry(i / 2) = Sheets(Rngadd(i)).Range(Rngadd(i + 1))
Next i


For i = 0 To UBound(RngAry)
For j = 0 To UBound(CritAry(i)) Step 2
RngAry(i).AutoFilter Field:=CritAry(i)(j), Criteria1:=CritAry(i)(j + 1)
Next j
With RngAry(i).Parent
vReturn(i) = .Range(.Cells(2, ColAry1(i)), .Cells(Rows.Count, ColAry1(i)).End(xlUp + 1)).SpecialCells(xlCellTypeVisible).Cells(1, 1)
End With
Next i


'Return values for Date and weekday
Worksheets("Holiday Staffing Planner").Activate
Range("D6").Value = vReturn(0)
Range("E6").Value = vReturn(1)


'Range 2
CritAry(1) = Array(1, InAry(3), 3, "<=" & InAry(4), 4, ">=" & InAry(4))

For i = 0 To UBound(RngAry)
For j = 0 To UBound(CritAry(i)) Step 2
RngAry(i).AutoFilter Field:=CritAry(i)(j), Criteria1:=CritAry(i)(j + 1)
Next j
With RngAry(i).Parent
vReturn(i) = .Range(.Cells(2, ColAry2(i)), .Cells(Rows.Count, ColAry2(i)).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1)
End With
Next i

Worksheets("Holiday Staffing Planner").Activate
Range("C12").Value = vReturn(8)
Range("C13").Value = vReturn(9)
Range("C14").Value = vReturn(10)
Range("C15").Value = vReturn(11)
Range("C16").Value = vReturn(12)
Range("C17").Value = vReturn(13)
Range("C18").Value = vReturn(14)
Range("C19").Value = vReturn(15)
Range("C20").Value = vReturn(16)
Range("C21").Value = vReturn(17)
Range("C22").Value = vReturn(18)
Range("C23").Value = vReturn(19)
Range("C24").Value = vReturn(20)
Range("C25").Value = vReturn(21)
Range("C26").Value = vReturn(22)
Range("C27").Value = vReturn(23)
Range("C28").Value = vReturn(24)
Range("C29").Value = vReturn(25)
Range("C30").Value = vReturn(26)
Range("C31").Value = vReturn(27)
Range("C32").Value = vReturn(28)
Range("C33").Value = vReturn(29)
Range("C34").Value = vReturn(30)
Range("C35").Value = vReturn(31)


End Sub


Any help is greatly appreciated!!


-Chris


Microsoft Misual Basic for Applications 7.1
Excel 2013
Windows 7 SP1
X64-based PC
Quad 2.40 GHz Intel Core i5-6300U
8 GB DDR

 

[Cooliozar]

I just wanted to update this post and apologize to who wasted time reading it and attempting to decode it. In trying to gain understanding for how to achieve a small task I succesfully ranted about the entire application needed as an end result. I wasn't attempting to have someone build the application for me. I just got caught up with eac subsequent portion as it tied into the next.

I've abandoned buildig this application using VBA and Excel and have moved to Cognos for a very simple Input/Output Report.

Next time I post, it will be much more specific and focused on a single problem I'm attempting to solve.

Thanks for your time!

-Chris