Hi,
I am looking for a unique way to set up an inner loop that will look at all the cells that a user would select to action. The current inner loop in my code will not fly. Please also note that the attached spreadsheet is only a snip. The actual sheet is 980 rows long. So please send your suggestions with this in mind.
As for the code I am using;
Thanks in advance Everyone for your help!
I am looking for a unique way to set up an inner loop that will look at all the cells that a user would select to action. The current inner loop in my code will not fly. Please also note that the attached spreadsheet is only a snip. The actual sheet is 980 rows long. So please send your suggestions with this in mind.
Report_Snip.xlsm | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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 | Group | Location | Report Name | Business Unit | Link | Status | Priority | Comments | Support | Team Lead | Refresh Cycle | Choice 1 | Choice 2 | Date Applied | Olink | Elink | Alink | Updated | Updated By | Audience | ID | Count | Path | Checked | ||
2 | CORE | North Building | abc.xlsx | Marketing | https://sharepoint.com/Marketing/abc.xlsx | Inactive | Low | Staff | Tom Fielding | Weekly | Update | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/21/2022 11:45 AM | Tom Fielding | 14532 | 1 | CLASSIFIED | Yes | ||||||
3 | CORE | Central Building | Employee_Asset.xlsx | Admin | https://sharepoint.com/Admin/Employee_Asset.xlsx | Active | Med | Dept | Suzanne Durrant | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/24/2022 9:30 AM | Michelle Fennings | 19452 | 1 | CLASSIFIED | Yes | |||||||
4 | CORE | DMZ | End Point Mappings.xlsx | IT | https://sharepoint.com/IT/End Point Mappings.xlsx | Active | High | Dept | Henry Nguyen | Bi-Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/11/2022 3:30 PM | Lazar Popovic | 12449 | 1 | CLASSIFIED | Yes | |||||||
5 | CORE | West Quad | Student Residence.xlsx | Residence | https://sharepoint.com/Residence/Student Residence.xlsx | Active | Low | Staff | Bob Hamilton | Weekly | Renew | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/21/2022 11:45 AM | Riston Comich | 14322 | 1 | CLASSIFIED | Yes | ||||||
6 | CORE | East Quad | Custodial_Projects 2022.xlsx | Facilities | https://sharepoint.com/Facilities/Custodial_Projects 2022.xlsx | Active | Med | Dept | Hector Rodriguez | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/24/2022 9:30 AM | Darlene Love | 19187 | 1 | CLASSIFIED | Yes | |||||||
7 | CORE | North Building | Sys_Org_diag.xlsx | IT | https://sharepoint.com/IT/Sys_Org_diag.xlsx | Active | High | Dept | Henry Nguyen | Bi-Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/11/2022 3:30 PM | Mark Kisel | 12439 | 1 | CLASSIFIED | Yes | |||||||
8 | CORE | South Point | Student_RAC_Membership.xlsx | Marketing | https://sharepoint.com/Marketing/Student_RAC_Membership.xlsx | Active | Low | Staff | Tom Fielding | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/21/2022 11:45 AM | Aaron Ladner | 14116 | 1 | CLASSIFIED | Yes | |||||||
9 | CORE | West Quad | Employee_Asset.xlsx | Admin | https://sharepoint.com/Admin/Employee_Asset.xlsx | Active | Med | Dept | Suzanne Durrant | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/24/2022 9:30 AM | Christopher Continental | 19762 | 1 | CLASSIFIED | Yes | |||||||
10 | CORE | East Quad | Cash Reconciliation Report.xlsx | Facilities | https://sharepoint.com/Facilities/Cash Reconciliation Report.xlsx | Active | High | Dept | Hector Rodriguez | Bi-Weekly | Renew | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/11/2022 3:30 PM | Enzo Ramoni | 12981 | 1 | CLASSIFIED | Yes | ||||||
11 | CORE | West Quad | E2E Cost Report.xlsx | Marketing | https://sharepoint.com/Marketing/E2E Cost Report.xlsx | Active | Low | Staff | Tom Fielding | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/21/2022 11:45 AM | Hailey Read | 14323 | 1 | CLASSIFIED | Yes | |||||||
12 | DMZ | Library | Dewey Decimal-Volumes by Floor.xlsx | Student Resources | \\dumexteam.dumex.com\webDavWWW\Dewey Decimal Volumes by Floor.xlsx | Inactive | Med | Dept | Christmas Martin | Weekly | Retire | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/24/2022 9:30 AM | Carlton Spence | 19017 | 1 | CLASSIFIED | Yes | ||||||
13 | DMZ | RAC Facilities | Gym Memberships 2022.xlsx | Recreation Facility | \\dumexteam.dumex.com\webDavWWW\Gym Memberships 2022.xlsx | Active | Low | Staff | Hannah Worthington | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/21/2022 11:45 AM | Robert Westall | 14791 | 1 | CLASSIFIED | Yes | |||||||
14 | DMZ | RAC Facilities | Gym Asset Tracker.xlsx | Recreation Facility | \\dumexteam.dumex.com\webDavWWW\Gym Asset Tracker.xlsx | Inactive | Med | Dept | Hannah Worthington | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/24/2022 9:30 AM | Harold Snell | 19286 | 1 | CLASSIFIED | Yes | |||||||
15 | CORE | East Quad | Supplies List.xlsx | Facilities | https://sharepoint.com/Facilities/Supplies List.xlsx | Active | High | Dept | Hector Rodriguez | Bi-Weekly | Retire | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/11/2022 3:30 PM | Colin George | 12115 | 1 | CLASSIFIED | Yes | ||||||
16 | DMZ | West Quad | Housing RSVP Fall 2023.xlsx | Residence | \\dumexteam.dumex.com\webDavWWW\Housing RSVP Fall 2023.xlsx | Active | Low | Staff | Bob Hamilton | Weekly | Retire | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/21/2022 11:45 AM | Yaroslav Popovic | 14360 | 1 | CLASSIFIED | Yes | ||||||
17 | CORE | North Building | SCOA Budgets.xlsx | Accounting | https://sharepoint.com/Accounting/SCOA Budgets.xlsx | Active | Med | Dept | Steven Fischerall | Weekly | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/24/2022 9:30 AM | Suzanne Durrant | 19802 | 1 | CLASSIFIED | Yes | |||||||
18 | CORE | 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 | CLASSIFIED | CLASSIFIED | CLASSIFIED | 04/11/2022 3:30 PM | Samantha Crozier | 12296 | 1 | CLASSIFIED | Yes | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E17:E18,E15,E2:E11 | E2 | =HYPERLINK("https://sharepoint.com/"&D2&"/"&C2) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
L2:L18 | List | Update, Retire, Renew |
As for the code I am using;
VBA Code:
Sub ExecuteUserRequest()
Dim ReportCount As Long
Dim ChosenReport As Integer
Dim x As Long
ReportCount = Range("B2").CurrentRegion.Rows.Count
ChosenReport = WorksheetFunction.CountA(Columns(12) - 1)
x = 2
Application.ScreenUpdating = False
Do While x <= ReportCount
For Each WorksheetFunction.CountA(Columns(13)) In Columns(13)
Select Case Cells(x, 12)
Case Renew
' perform action
Case Retire
' peform action
Case Update
' perform action
End Select
Next
x = x + 1
Loop
Application.ScreenUpdating = True
End Sub
Thanks in advance Everyone for your help!