collections

  1. szita2000

    Excel VBA copy-find-paste. Use of collections or Dictionaries?

    Hi All. Hope you all safe and good in these Covid times. I have a task ahead of me and I am thinking that maybe there is a better way to code this. I have two workbooks. Source and target. I need to pull out values from the source and paste it to the target, It is not a block per say, but...
  2. D

    Calculate collection date for different customers based on established payment behaviours

    Hi Everyone, I'm working on a cashflow projection and for that I need to determine a realistic date for collections. I have a list of invoices issued to customers, each with corresponding due date. However, many customers disregard the due date and pay according to their established payment...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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)...
  19. 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!
  20. 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...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top