Using array to quickly loop through data

[mongoose36]

Hi all,

I've built a simple inventory tracking system, and decided a reporting feature would be nice. There are four categories that are entered when inventory is removed...Date, Employee Name, Item Description, Location, and quantity.

Four my reporting purposes I'm only concerned with Date, Employee name and Item Description.

I've been able to write code that does what I want using a multiple Cases and a For loop once the case is identified. However, the more data there is the longer this takes...so I decided to stretch myself and try my hand at arrays (first time really working with arrays), but I'm having trouble figuring out exactly what I need to do.

Here is what I think the steps need to be.

1. Store my data (the categories above) which are located in the Check Out sheet
2. Go through the arrayed data to find exact matches based on my search criteria (here is where the Cases come in)
3. Pull out only that data and write the information to a "Report" Sheet
4. Export that sheet to PDF (this part I already have)

Below is a copy of what my current "working" code looks like (I should mention that the search selections are made from a userform this is what the Cases are deciphering between which ones are blank etc...), also most of my variables are instantiated as Public variables within a Public_Variables module also below.

Select Case EmpList
    Case Is = vbNullString 'case when employee is not selected
        Select Case ItemBox
            Case Is = vbNulString 'When No Item is Selected
                Select Case IsDt
                    Case Is = True 'No Item Is Selected but there is a date
                        For Each Cel In CCDate
                            If Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is not selected
            Case Is <> vbNulString 'Item is Selected
                Select Case IsDt
                    Case Is = True 'Item is selected with date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 2).Value = ItemBox.Text And Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                    Case Is = False 'Item is selected without date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 2).Value = ItemBox.Text Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is selected
        End Select 'ItemBox
    Case Is <> vbNullString 'Case when Employee is selected
        Select Case ItemBox
            Case Is = vbNulString 'When employee is selected but no Item is Selected
                Select Case IsDt
                    Case Is = True 'Employee is selected, no Item Is Selected, but there is a date range
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text And Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                    Case Is = False
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is not selected
            Case Is <> vbNulString 'Employee is selected and Item is Selected
                Select Case IsDt
                    Case Is = True 'Employee is selected, Item is selected with date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text And Cel.Offset(0, 2).Value = ItemBox.Text And Cel.Value >= FromDate Or Cel.Value <= ToDate Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                    Case Is = False 'Employee is selected, Item is selected without date
                        For Each Cel In CCDate
                            If Cel.Offset(0, 1).Value = EmpList.Text And Cel.Offset(0, 2).Value = ItemBox.Text Then
                                Cel.EntireRow.Copy
                                RepRng.Offset(1, 0).PasteSpecial
                                Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
                            End If
                        Next
                        Application.CutCopyMode = False
                End Select 'IsDt for case when item is selected
        End Select 'ItemBox
End Select 'EmpList

And the Public Variables...thanks in advance!!

Option Explicit
Public IsDt As Boolean
Public wbInv As Workbook
Public MaintEmp, ItmLst, CheckOut, wsReport As Worksheet
Public EmpRange, Emp, CatRange, ItemRange, Cat, COCel, ItmBB, CCDate, CCEmp, CCItem, RepRng As Range
Public FromDate, ToDate As Date
Public Cel, ClearRange As Range
Sub PubVar()
    Set wbInv = ThisWorkbook
    Set MaintEmp = wbInv.Worksheets("Maintenance Employees")
    Set ItmLst = wbInv.Worksheets("Item List")
    Set CheckOut = wbInv.Worksheets("Check out List")
    Set wsReport = wbInv.Worksheets("Report")
    
    Set EmpRange = MaintEmp.Range("A2", MaintEmp.Range("A" & Rows.Count).End(xlUp))
    Set CatRange = ItmLst.Range("A2", ItmLst.Range("A" & Rows.Count).End(xlUp))
    Set ItemRange = ItmLst.Range("B2", ItmLst.Range("B" & Rows.Count).End(xlUp))
    Set COCel = CheckOut.Range("A" & Rows.Count).End(xlUp)
    Set CCDate = CheckOut.Range("A2", CheckOut.Range("A" & Rows.Count).End(xlUp))
    Set RepRng = wsReport.Range("A" & Rows.Count).End(xlUp)
    
    
End Sub

[Vikas_Gautam]

Hi cplettner,
if you want to make an array of Date, Employee name and Item Description..
then there is a suggestion for code..

dim Arr()
Redim Arr(0 to 2 , 0 to 0)
'Now start a loop in for populating the array...
c=0
for each cell in yourRange
Redim Preserve arr(0 to 2 , 0 to c)
Arr(0,c)=yourRange.value or offset.value
Arr(1,c)=yourRange.value or offset.value
Arr(2,c)=yourRange.value or offset.value
c=c+1
next
'array populated..
Dim Arr2(0 to Ubound(Arr), 0 to 2)
Arr2=Worksheetfunction.transpose(Arr)

Arr2 is the fully populated array...
now you can apply your conditions on it...


Say thanks, Click *

[mongoose36]

Vikas_Gautam thanks for the response!!

I guess I'm not really sure how to take arrays. What exactly does it do? I understand a For loop when the code iterates through each cell in a range and then you can look for certain data or you can use the offset to look for data in adjacent cells, but I'm not quite sure how to apply this to arrays. would you mind explaining the code above.

I understand declaring the array, but why Redim as you do?? in the syntax "Redim Arr(0 to 2, 0 to 0)" are the 0 to 2 and 0 to 0 coordinates?

for the variable "yourRange" do I set that to only column A (this is where the dates are) using

CheckOut.Range("A2", CheckOut.Range("A" & Rows.Count).End(xlUp))

or to the whole sheet with

CheckOut.Range("A2", CheckOut.Range("E" & Rows.Count).End(xlUp))

For the section Arr(0,c)...(1,c)...(2,c) is the code iterating through cells or perhaps the 0, 1, 2 are three different arrays, but how does the code populate the arrays when it doesn't seem like you are iterating through the cells?

I'm guessing the function.transpose command at the end is because the rows and columns the array are mixed up??

Sorry for all the questions, but I really want to understand this through and through.


Thanks

[mongoose36]

Ok Here's what I have so far (can't remember which forum I found this bit of code). It's not like yours, but I wasn't quite sure what was going on there so I did some digging.

Sub test()
Call PubVar
Dim i, j, r, c As Long
Dim rng As Range
Dim a
Dim a2() As Variant
Dim rws As Long, cols As Long
r = 1
c = 1

a = CheckOut.Range("A2", CheckOut.Range("E" & Rows.Count).End(xlUp))         'a will now hold all the values from Table1
rws = UBound(a, 1)  'This is how many rows in the array
cols = UBound(a, 2) ''This is how many columns in the array
For i = 1 To rws
  For j = 1 To cols
    'reference each element of the array by a(i,j) which gives you
    'the value from the ith row and jth column
        If a(i, j) = "Test" Then
            For c = 1 To 5
                a2(r, c) = a(i, c)
            Next c
            r = r + 1
        End If
  Next j
Next i
wsReport.Range("A2") = a2
End Sub

I'm getting stuck when I try to pass the element value to the other array (I'm doing this so I can create an array with only the values I want to print to another range ... if there is a better way I'm open.)

[mongoose36]

I figured it out!