Array Memory Error

CCSlice

CCSlice

New Member
Joined
Feb 11, 2022
Messages
20
Office Version
  1. 2016
Platform
  1. 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.


Rich (BB code): 
Report_Snip.xlsb
ABCDEFGHIJKLMNOPQRSTUVWX
1LocationReport NameBusiness UnitLinkStatusPriorityCommentsSupportTeam LeadRefresh CycleChoice 1ResultTime StampDate AppliedOlinkElinkAlinkUpdatedUpdated ByAudienceIDCountPathChecked
2North Buildingabc.xlsxMarketinghttps://sharepoint.com/Marketing/abc.xlsxInactiveLowStaffTom FieldingWeeklyRenew4/21/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/21/2022 11:45 AMTom Fielding145321CLASSIFIEDYes
3Central BuildingEmployee_Asset.xlsxAdminhttps://sharepoint.com/Admin/Employee_Asset.xlsxActiveMedDeptSuzanne DurrantWeekly4/24/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/24/2022 9:30 AMMichelle Fennings194521CLASSIFIEDYes
4DMZEnd Point Mappings.xlsxIThttps://sharepoint.com/IT/End Point Mappings.xlsxActiveHighDeptHenry NguyenBi-Weekly4/11/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/11/2022 3:30 PMLazar Popovic124491CLASSIFIEDYes
5West QuadStudent Residence.xlsxResidencehttps://sharepoint.com/Residence/Student Residence.xlsxActiveLowStaffBob HamiltonWeekly4/21/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/21/2022 11:45 AMRiston Comich145321CLASSIFIEDYes
6East QuadCustodial_Projects 2022.xlsxFacilitieshttps://sharepoint.com/Facilities/Custodial_Projects 2022.xlsxActiveMedDeptHector RodriguezWeekly4/24/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/24/2022 9:30 AMDarlene Love194521CLASSIFIEDYes
7North BuildingSys_Org_diag.xlsxIThttps://sharepoint.com/IT/Sys_Org_diag.xlsxActiveHighDeptHenry NguyenBi-Weekly4/11/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/11/2022 3:30 PMMark Kisel124491CLASSIFIEDYes
8South PointStudent_RAC_Membership.xlsxMarketinghttps://sharepoint.com/Marketing/Student_RAC_Membership.xlsxActiveLowStaffTom FieldingWeekly4/21/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/21/2022 11:45 AMAaron Ladner145321CLASSIFIEDYes
9West QuadEmployee_Asset.xlsxAdminhttps://sharepoint.com/Admin/Employee_Asset.xlsxActiveMedDeptSuzanne DurrantWeekly4/24/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/24/2022 9:30 AMChris Continental194521CLASSIFIEDYes
10East QuadCash Reconciliation Report.xlsxFacilitieshttps://sharepoint.com/Facilities/Cash Reconciliation Report.xlsxActiveHighDeptHector RodriguezBi-Weekly4/11/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/11/2022 3:30 PMEnzo Ramoni124491CLASSIFIEDYes
11West QuadE2E Cost Report.xlsxMarketinghttps://sharepoint.com/Marketing/E2E Cost Report.xlsxActiveLowStaffTom FieldingWeekly4/21/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/21/2022 11:45 AMHailey Read145321CLASSIFIEDYes
12LibraryResource Allocations-Volumes by Floor.xlsxStudent Resourceshttps://sharepoint.com/Student Resources/Resource Allocations-Volumes by Floor.xlsxInactiveMedDeptChristmas MartinWeeklyRenew4/24/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/24/2022 9:30 AMCarlton Spence194521CLASSIFIEDYes
13RAC FacilitiesGym Memberships 2022.xlsxRecreation Facilityhttps://sharepoint.com/Recreation Facility/Gym Memberships 2022.xlsxActiveLowStaffHannah WorthingtonWeekly4/21/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/21/2022 11:45 AMRobert Westall145321CLASSIFIEDYes
14RAC FacilitiesGym Asset Tracker.xlsxRecreation Facilityhttps://sharepoint.com/Recreation Facility/Gym Asset Tracker.xlsxInactiveMedDeptHannah WorthingtonWeekly4/24/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/24/2022 9:30 AMHarold Snell194521CLASSIFIEDYes
15East QuadSupplies List.xlsxFacilitieshttps://sharepoint.com/Facilities/Supplies List.xlsxActiveHighDeptHector RodriguezBi-Weekly4/11/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/11/2022 3:30 PMColin George124491CLASSIFIEDYes
16West QuadHousing RSVP Fall 2023.xlsxResidencehttps://sharepoint.com/Residence/Housing RSVP Fall 2023.xlsxActiveLowStaffBob HamiltonWeekly4/21/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/21/2022 11:45 AMYaroslav Popovic145321CLASSIFIEDYes
17North BuildingSCOA Budgets.xlsxAccountinghttps://sharepoint.com/Accounting/SCOA Budgets.xlsxActiveMedDeptSteven FischerallWeeklyRetire4/24/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/24/2022 9:30 AMSuzanne Durrant194521CLASSIFIEDYes
18Central BuildingCost Benefit Analyses_Spring 2022.xlsxIThttps://sharepoint.com/IT/Cost Benefit Analyses_Spring 2022.xlsxActiveHighDeptHenry NguyenBi-Weekly4/11/2022CLASSIFIEDCLASSIFIEDCLASSIFIED04/11/2022 3:30 PMSamantha Crozier124491CLASSIFIEDYes
Sheet1
Cell Formulas
RangeFormula
D2:D18D2=HYPERLINK("https://sharepoint.com/"&C2&"/"&B2)
Cells with Data Validation
CellAllowCriteria
K2:K18ListUpdate, 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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
I'm not sure what you're trying to do here. But I'm guessing you need to write a nested loop through all columns from 1 to UBound(InputArray, 2)?

Rich (BB code): 
ActiveInputArray(SelectedReportCount, Columns) = InputArray(i, Columns)

You'll need to change the variable name!
 
Upvote 0
Solution
You don't have Columns defined or set anywhere. Also you should not use it as a variable since it has a meaning in its own right in VBA.

Just entering this will give you the out of memory you are getting:
VBA Code: 
Debug.Print Columns
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Opening SharePoint Workbooks
Replies
3
Views
442
jkpieterse
jkpieterse
S
  • Question
Setting Inner Loop
Replies
0
Views
292
Slicemahn
S
CCSlice
  • Solved
Excel Sheet too Large - Need to redefine Range
Replies
7
Views
508
johnnyL
J
D
  • Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
501
Herakles
H
gtd526
  • Solved
not to Index, Aggregate if a value is present, NBA InjuryList
Replies
3
Views
328
Fluff
Fluff

Forum statistics

Threads
1,214,936
Messages
6,122,340
Members
449,079
Latest member
rocketslinger

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top