+ Reply to Thread
Results 1 to 5 of 5

Using array to quickly loop through data

  1. #1
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Using array to quickly loop through data

    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.
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  2. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Using array to quickly loop through data

    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 *

  3. #3
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Using array to quickly loop through data

    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

  4. #4
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Using array to quickly loop through data

    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.)
    Last edited by mongoose36; 07-07-2014 at 12:13 AM.

  5. #5
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Using array to quickly loop through data

    I figured it out!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Moving dictionary items to an 2D array that can be placed into worksheet quickly
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-22-2014, 01:21 PM
  2. Loop new messages containing a table, populate a dynamic array, paste array to Excel
    By laripa in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2013, 07:20 AM
  3. Finding duplicate data quickly in a row?
    By lesliepear in forum Excel General
    Replies: 7
    Last Post: 12-05-2009, 09:50 PM
  4. Data Validation=> List=> code for quickly finding data
    By goodgirl1982 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2007, 03:39 AM
  5. Convert DATA into BARCODE 128 B quickly
    By Safi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2006, 08:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1