collection

How to make it indexed? and How to retrieve it using item.index? P.S: First time on Collection.

Hi all! I wonder could someone help me, I am trying to produce an activity analysis database for my team. We track our activity in our own spreadsheets, in total there is 16 of them. Each process has a subset of different activites, an example of which can be seen below: file path...

Hello, I'm working on a project that requires me working with files and directories. I haven't had any issues yet with setting up user forms, letting users set file paths to folders or even selecting and importing files, etc... but I'm not sure how to grab all the files in a particular folder...

Hi All, I have a module that is trying to store classes in a collection based off selections in a userform and I'm a little stuck. For illustrative purposes I've simplified it. MainTest is my main module Sub MainTest() Dim colTest As New Collection fTest.Show ..Does stuff with the...

Is there a collection that holds all sheets: work- chart- dialog- ? What would be the DIM datatype of a member? (none of the intellitype options seem to fit) Also, when I add the use of the dialog() collection VBA says it can't recognize the whole sub() Tester I am using Sub...

Dear Excel friends, I have the following task: I need to find a way to collect (copy) all rows that have a specific value (the value is always in the same known column - e.g. all rows that have the value "applicable" in column C). The problem is that there are rows that are empty or have...

I'm having a hard time getting this collection thing down. What I want this code to do is take the user input as a string and store it for use later when the For-Each code is run. I'm not having any luck so far and its probably because I'm having trouble wrapping my head around the concept...

I was wondering if it is possible to use a For Each-Next construct to cycle through each element of an array, as one does with object collections? In other words, can an array and its elements be treated as objects, or is the only option to use a nested For-Next construct with the appropriate...

I have a tricky VBA question concerning Arrays (or possibly Collections): I would like to use two values located on the same row from Array1 (Sheet1) to search for the first instance of those same two values on the same row found in Array2 (Sheet2). 1) find the first instance of two values on...

How do you write the contents of a collection as a range instead of outputting cell by cell? I would like to write all 50 values with one command, unlike below. Thanks. Dim mycol As Collection: Set mycol = New Collection ' fill the collection For x = 1 To 50...

I read Bill's "VBA and Macros in Excel 2007" chapter on Conditional Formatting. That said, I'm finding that iterating through the ConditionalFormats.Formula1 collection to not work as expected. For example, a cell shows in the User Interface the following three rules: Rule 1...

So currently, I use a for each loop to output my collection. I was wondering if there was a way to dump all the data within it in one shot rather than using a loop?

I am trying to learn how to get classes and collections to work together with userforms. How can I call a class module's stuff from a userform module? The userform calls a macro from a different regular module successfully. The class module is public not private. The userform runs...

Hello; i have one question: what is maximum size of collection in vba ? The Debugger shows only the 256 items. Is size only 256 items ? or debugger shows only 256 items and size is without limits ?

Hi guys, Does somebody knows if you can search for controls in a userform collection, like in: / Dim ctl as Control / For Each ctl In UserForm1.Controls /"do action" but can exclude one specific subset of the collection of the action ? Like the subset: /...

Using collections is something I picked upon just recently, so I'm having a little trouble understanding why the current function returns a #Name error. Basically what I'm trying to do is add the y1 and y2 variables into a collection called ygroup. I'm then using a For Each loop to do a...

Hi all, I've created the function below to create a collection of primes which is based on the Sieve of Erastothenes. The function creates the list fairly quickly; it can produce all primes under one million in less than a second on my PC. Using the collection, I want to be able to A) Test a...

how can i get Intellisense for collection items? example: MyCollection(Key).SomeProperty Where MyCollection(Key) returns a custom object (a custom class instance) Here's how to make a Custom Collection: http://www.dailydoseofexcel.com/archives/2010/07/04/custom-collection-class/ cheers! johny

I'm trying to add values in a range to a collection. here's the range: <TABLE style="WIDTH: 103pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=137 x:str><COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR...

I have a fairly easy question. I have scores ranging from 0-100. Lets just say I have 8 of them from A:1-H:1. I want in I:1 to calculate the percentage of all scores greater than 85. So out of all the scores it is is 85 and above then it will give me the percentage of that. So if only 4 scores...