CCSlice
New Member
- Joined
- Feb 11, 2022
- Messages
- 20
- Office Version
- 2016
- Platform
- Windows
Hi Mr. Excel Community!
I am having challenges with my spreadsheet. It is a spreadsheet where a person selects whether a report is to be retired, renewed, or updated. The data in the spreadsheet is not real but similar. I am getting memory errors with my array to capture the selected reports.
Thanks in advance for your help!
I am having challenges with my spreadsheet. It is a spreadsheet where a person selects whether a report is to be retired, renewed, or updated. The data in the spreadsheet is not real but similar. I am getting memory errors with my array to capture the selected reports.
Rich (BB code):
Report_Snip.xlsb
A B C D E F G H I J K L M N O P Q R S T U V W X 1 Location Report Name Business Unit Link Status Priority Comments Support Team Lead Refresh Cycle Choice 1 Result Time Stamp Date Applied Olink Elink Alink Updated Updated By Audience ID Count Path Checked 2 North Building abc.xlsx Marketing https://sharepoint.com/Marketing/abc.xlsx Inactive Low Staff Tom Fielding Weekly Renew 4/21/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/21/2022 11:45 AM Tom Fielding 14532 1 CLASSIFIED Yes 3 Central Building Employee_Asset.xlsx Admin https://sharepoint.com/Admin/Employee_Asset.xlsx Active Med Dept Suzanne Durrant Weekly 4/24/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/24/2022 9:30 AM Michelle Fennings 19452 1 CLASSIFIED Yes 4 DMZ End Point Mappings.xlsx IT https://sharepoint.com/IT/End Point Mappings.xlsx Active High Dept Henry Nguyen Bi-Weekly 4/11/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/11/2022 3:30 PM Lazar Popovic 12449 1 CLASSIFIED Yes 5 West Quad Student Residence.xlsx Residence https://sharepoint.com/Residence/Student Residence.xlsx Active Low Staff Bob Hamilton Weekly 4/21/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/21/2022 11:45 AM Riston Comich 14532 1 CLASSIFIED Yes 6 East Quad Custodial_Projects 2022.xlsx Facilities https://sharepoint.com/Facilities/Custodial_Projects 2022.xlsx Active Med Dept Hector Rodriguez Weekly 4/24/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/24/2022 9:30 AM Darlene Love 19452 1 CLASSIFIED Yes 7 North Building Sys_Org_diag.xlsx IT https://sharepoint.com/IT/Sys_Org_diag.xlsx Active High Dept Henry Nguyen Bi-Weekly 4/11/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/11/2022 3:30 PM Mark Kisel 12449 1 CLASSIFIED Yes 8 South Point Student_RAC_Membership.xlsx Marketing https://sharepoint.com/Marketing/Student_RAC_Membership.xlsx Active Low Staff Tom Fielding Weekly 4/21/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/21/2022 11:45 AM Aaron Ladner 14532 1 CLASSIFIED Yes 9 West Quad Employee_Asset.xlsx Admin https://sharepoint.com/Admin/Employee_Asset.xlsx Active Med Dept Suzanne Durrant Weekly 4/24/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/24/2022 9:30 AM Chris Continental 19452 1 CLASSIFIED Yes 10 East Quad Cash Reconciliation Report.xlsx Facilities https://sharepoint.com/Facilities/Cash Reconciliation Report.xlsx Active High Dept Hector Rodriguez Bi-Weekly 4/11/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/11/2022 3:30 PM Enzo Ramoni 12449 1 CLASSIFIED Yes 11 West Quad E2E Cost Report.xlsx Marketing https://sharepoint.com/Marketing/E2E Cost Report.xlsx Active Low Staff Tom Fielding Weekly 4/21/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/21/2022 11:45 AM Hailey Read 14532 1 CLASSIFIED Yes 12 Library Resource Allocations-Volumes by Floor.xlsx Student Resources https://sharepoint.com/Student Resources/Resource Allocations-Volumes by Floor.xlsx Inactive Med Dept Christmas Martin Weekly Renew 4/24/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/24/2022 9:30 AM Carlton Spence 19452 1 CLASSIFIED Yes 13 RAC Facilities Gym Memberships 2022.xlsx Recreation Facility https://sharepoint.com/Recreation Facility/Gym Memberships 2022.xlsx Active Low Staff Hannah Worthington Weekly 4/21/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/21/2022 11:45 AM Robert Westall 14532 1 CLASSIFIED Yes 14 RAC Facilities Gym Asset Tracker.xlsx Recreation Facility https://sharepoint.com/Recreation Facility/Gym Asset Tracker.xlsx Inactive Med Dept Hannah Worthington Weekly 4/24/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/24/2022 9:30 AM Harold Snell 19452 1 CLASSIFIED Yes 15 East Quad Supplies List.xlsx Facilities https://sharepoint.com/Facilities/Supplies List.xlsx Active High Dept Hector Rodriguez Bi-Weekly 4/11/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/11/2022 3:30 PM Colin George 12449 1 CLASSIFIED Yes 16 West Quad Housing RSVP Fall 2023.xlsx Residence https://sharepoint.com/Residence/Housing RSVP Fall 2023.xlsx Active Low Staff Bob Hamilton Weekly 4/21/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/21/2022 11:45 AM Yaroslav Popovic 14532 1 CLASSIFIED Yes 17 North Building SCOA Budgets.xlsx Accounting https://sharepoint.com/Accounting/SCOA Budgets.xlsx Active Med Dept Steven Fischerall Weekly Retire 4/24/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/24/2022 9:30 AM Suzanne Durrant 19452 1 CLASSIFIED Yes 18 Central Building Cost Benefit Analyses_Spring 2022.xlsx IT https://sharepoint.com/IT/Cost Benefit Analyses_Spring 2022.xlsx Active High Dept Henry Nguyen Bi-Weekly 4/11/2022 CLASSIFIED CLASSIFIED CLASSIFIED 04/11/2022 3:30 PM Samantha Crozier 12449 1 CLASSIFIED Yes Sheet1
Cell Formulas Range Formula D2:D18 D2 =HYPERLINK("https://sharepoint.com/"&C2&"/"&B2)
Cells with Data Validation Cell Allow Criteria K2:K18 List Update, Retire, Renew
VBA Code:
Sub Collect_Actions()
Dim strFile$
Dim DataStartRow As Long, SelectedReportCount As Long, UpdtCount As Long
Dim ActiveInputArray As Variant, InputArray As Variant
Application.ScreenUpdating = False
DataStartRow = 2
InputArray = Worksheets("Sheet1").[A1].CurrentRegion
ReDim ActiveInputArray(1 To UBound(InputArray, 1), 1 To UBound(InputArray, 2))
For i = DataStartRow To UBound(InputArray)
SelectedReportCount = 0
If InputArray(i, 11) <> "" Then
SelectedReportCount = SelectedReport + 1
ActiveInputArray(SelectedReportCount, Columns) = InputArray(i, Columns)
End If
Next
' Set conditions for Update, Retire, and Renew
UpdtCount = 1
Do While UpdtCount <= SelectedReportCount
strFilePath$ = ActiveInputArray(UpdtCount, 4)
Select Case ActiveInputArray(UpdtCount, 11)
Case "Renew"
Application.StatusBar = ActiveInputArray(UpdtCount, 2) & "is currently undergoing " & ActiveInputArray(UpdtCount, 4) & "ing process..."
ActiveInputArray(UpdtCount, 5) = "Active"
ActiveInputArray(UpdtCount, 12) = "Request Complete"
ActiveInputArray(UpdtCount, 13) = Format(Now, "mm/dd/yyyy hh:mm AM/PM")
Application.StatusBar = ActiveInputArray(UpdtCount, 2) & " has been renewed."
Case "Retire"
Application.StatusBar = ActiveInputArray(UpdtCount, 2) & "is currently undergoing the " & ActiveInputArray(UpdtCount, 4) & "process..."
ActiveInputArray(UpdtCount, 5) = "Inactive"
ActiveInputArray(UpdtCount, 12) = "Request Complete"
ActiveInputArray(UpdtCount, 13) = Format(Now, "mm/dd/yyyy hh:mm AM/PM")
Application.StatusBar = ActiveInputArray(UpdtCount, 2) & " has been retired."
End Select
Loop
Application.ScreenUpdating = True
End Sub
Thanks in advance for your help!