collections

  1. T

    For Each Loop

    Following from this thread: https://www.mrexcel.com/forum/excel-questions/1114405-collections.html I was told this piece of information: Syntax ... For Each element In group ... element ... For collections, element can only be a Variant variable, a generic object variable, or any...
  2. K

    Formula Question

    Hi Guys, Could someone explain how I would merge these two formulas below so that it looks in one range and returns ROUTE, then looks in another and returns COLLECTIONS, I'm slowly getting my head around formulas but bringing them together is what I struggle with...
  3. R

    Simple Class module example required

    Help Can anyone provoide me with a very simple Class module example that captures for example peoples first names in column "A", Surnames in column "B" and loops through that column and adds them to a collection. I think if i had a bog standard example i might actually get it! Then I can build...
  4. B

    Automation Error when building collection

    Dear Excel Experts, I have a simple piece of VBA code with some (messy) error handling that builds a collection by constructing a string and seeing if it exists in a larger collection. This code generates an application execution error on the second pass through the for next loop. Private...
  5. D

    Export sheet collection to pdf

    Greetings, I need some assistance with sheet collections. I have a large spreadsheet that our designers copy onto each job file and use. The first 103 sheets do not get printed or exported to pdf. We only send out the new generated pages. I have written code to select all sheets from sheet #104...
  6. ClimoC

    Collection of UserForms at Runtime - Opening and Closing?

    Hello I've been around the woodwork with this one, so hopefully I haven't gone totally off track. Got the feeling I'm straying into the OO problems of VBA/VB6... Desired Outcome: Creating (no more than 2 at any one time) multiple instances of the same Userform, at runTime, and then referring...
  7. E

    Instead of 3 cells collections -3 columns

    Wonder if someone can help me. I have the following code where collections are made on 3 cells H2 I2 J2 -if I want the collections to be on 3 columns instead of 3 cells what code must change.The collections get checked on column A1-15 and B1-15 respectively.So the collections must be on 3...
  8. P

    EXCEL VBA - Loop through range/collection

    Hi, I have this set of data <tbody> Car Ford Mondeo Ford Fiesta Honda Civic Toyota Prius </tbody> <tbody> brands Ford Honda </tbody> I try to write a macro which adds brands into collection/range and then check, if there is this substring (brand) in a range. Any hints...
  9. A

    VBA pass collection to collection by value, not by reference.

    I am learning VBA and trying to master collections. I am using collections because the ability to remove items seems likes it gives an advantage over arrays when using 2D. I am looking to compare source 1 to source 2. I load both sets into collections. Sample Data source 1 <tbody> A B c...
  10. S

    How to use multiple Directories using VBA

    Hello, I am trying to write a code that will go in and compare 2 statements together and see if they are formatted correctly. To do this it requires me to look in two different folders. I was able to make one directory and retrieve files from it. But when I added a second directory, my macro is...
  11. Caleeco

    Remove Duplicates from a Range

    Hello, I am attempting to remove duplicates from a set range. However, I need it to be only the range defined and not the entire column. I read an example online whereby a unique list is created by adding each row of data to a collection (resume next on errors). My code below correctly...
  12. G

    Issues with Strings from Excel Collections

    Hello all, I'm indexing some text files to get some data and pull it into excel. The file format I'm pulling from is a ".txt". The issue I'm running into is when VBA pastes in the string from the data collection. The immediate window using debug.print shows exactly how I want to see my data...
  13. J

    VBA Collections into Collections

    I'm trying to make a collection out of a list of items in cells. Each cell may or may not have a few items separated by ","'s... I've got that part down...At least I think... but then I get to line 9, my DontDupe collection is empty... I expected it to possibly just hold the last added...
  14. R

    VBA Collection Items Get Overwritten

    I am attempting to add form controls dynamically to a blank form, and build a collection of those controls so I can write my own events. I'm having limited success. The controls are successfully adding to the form, appear to be adding to the collection, but my click() event only works for the...
  15. S

    Identify a single item in a files collection without a For Each ... Next

    Hi Guys..... I strongly suspect that the answer to this will be blindingly obvious, but here goes.... The following code is the preamble to code which offers a filepicker if there is more than one file in a folder. Having identified that there is just one file in the folder, the following...
  16. S

    VBA - adding by value to collection

    I want create some objects of a user-defined class (clsDataSet) and put them into a collection. I.e. something like this: Dim col As Range Dim allDataPoints As New Collection For Each col In RangeIn.Columns Dim Series As New clsDataSet Series.Initialise (col)...
  17. J

    Collections and Classes

    Hi, Can anyone help me with this? I have a class, lets say with Name, Address and Salary properties. And a Collection (Employees) with all the classes. Is there a way to search for the maxvalue of the Salary, from all the classes in the collection? Thanks!
  18. P

    How to create Login and Password when opening Workbook using Collections

    I am trying to make a login prompt when opening a workbook. In this workbook I have a Sheet labeled, "Userbase". In the "Userbase" sheet there are 4 columns. In cell "A2", the text "First Name" is set. In cell "B2", the text "Last Name" is set. In cell "C2", the text "Logon ID" is set. In...
  19. J

    .FormatCondition in Excel 2010 not working

    Actually, I am sure I just don't know how it works well enough to make it work, but hope the expertise here will be enough to correct my misdirection. I am using xlUnique to find unique values comparing two spreadsheets' keys. I copy the range from each sheet into a new worksheet to do the...
  20. K

    Arrays or Collections for data manipulation?

    Hi, I have developed a workbook for estimating project costs based on selected Labour, Material, and Plant components, but I've recently dicovered classes and collections and was wondering whether they could be used to develop a more efficient solution. The data is multi-dimensional, i.e...

Some videos you may like

This Week's Hot Topics

Top