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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[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..

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

or to the whole sheet with

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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!