collection

  1. M

    Adding a "Set Range" line to my function - Count Words in column

    Hi! I found a code on Stackoverflow (3. comment): https://stackoverflow.com/questions/33902129/count-the-most-frequently-used-words-in-an-excel-column-containing-a-lot-of-text But I can't use, because if I add a "Set RangeToCheck= Range("A1:A4") it doesn't work. If i click the "run" button, it...
  2. B

    Data ID Question

    First off I am a very green user to access, I have a generic access database that a clerk fills out a form and that form feeds a collection table called "employeecollection". I have a program that allows me to have a bar code on a employee identification card who's number is based off my...
  3. B

    How to Add elements to my Array?

    Dears, I've the following Macro which is responsible for finding the airport into a TXT. file. It's working perfectly, when the airport is found the macro returns the airport information, at the first line. Sub FindAirportDep() Dim fn As String, txt As String, delim As String, a() As String...
  4. Rymare

    Loop through VBA collection

    I have an excel with a list of paths: This list can change length--sometimes there are 100 .xlsx files, sometimes 4, etc. But always xlsx and always in the same sheet in my workbook (Project Specialist Compile, sheet ghgh). The paths are not fixed, they constantly change so though the list...
  5. D

    Getting a list of the cells in a range (VBA)

    In VBA, if I have a range, how do I get the list of individual cells in the range? The only thing I have in mind is to use a "For Each" loop but that sometimes seems odd. For example, I want to make a macro that swap two cells: one select two cells and run the macro. Here is what I have written...
  6. P

    Dynamic Reference a collection

    I have six collections of auto generated user form controls Dim MainLabels As Collection Dim Metric1 As Collection Dim Metric2 As Collection Dim Metric3 As Collection Dim Metric4 As Collection Dim Metric5 As Collection And I know I can add a control with this type of code: MainLabels.Add...
  7. B

    listbox.selected(x)=true not working correctly

    So I have created a similar filter feature on a userform that imitates that used in excel when you do the filter feature. My code creates a frame with a listbox that fills the listbox with a bunch of items and when the user selected the ones to filter on and hits submit it loads these selected...
  8. S

    Finding the mean value of a collection...or array?

    Greetings, I am adding integer values to a collection. After adding all the values meeting the add criteria I would like to take the average value of the integer and output it to a sheet. I am unsure of how to calculate the mean value of the contents in a collection. Would this be easier to...
  9. K

    [VBA] Sorting Values but Referencing Original Position (without index-match)

    Hello everyone. For simplicity, I have a VBA loop which changes the range of cells being referenced during each iteration. For each loop I'm trying to find the largest 10 values and smallest 10 values from the range. The tricky part is I need to reference where those values are located in the...
  10. T

    Using collection instead of array

    I have 10 rows of data in column A. I can calculate the upper quartile as follows: Dim MyArray() As Variant MyArray() = Cells(1, 1).CurrentRegion.Value Dim UQ As Double UQ = Application.WorksheetFunction.Quartile_Exc(MyArray(), 3) If instead of using an array, I choose to use a...
  11. R

    Macro to open and populate email

    Hi guys, I'm still relatively new to VBA but I have been following a few threads on here and hope you may be able to help. I'm currently working on new travel booking spreadsheet for my company and am looking for a macro that will do the following on a selected row. 1) Open a new email...
  12. T

    Return values from collection to worksheet

    Normally when I use arrays, this can simply be returned in a single statement, such as: Cells(1, 1).Resize(MyArrayRows, MyArrayCols).Value= MyArray() If I were to use classes and collections to make my code easier to read, as per this article...
  13. S

    Defining a Range Using Elements From a Collection

    Greetings, Caution: this post is related to a previous post, but due to the previous post lacking all critical elements contributing to the conflict I am posting again with new material information, namely the collection component to the problem. I am trying to define a range that incorporates...
  14. S

    Adding to a New Collection

    Greetings, I am running into some issues while adding a new collection. The collection portion of my script is below: Dim col As New CollectionDim col2 As New Collection col.Add b col.Add c col.Add D col.Add E col.Add F col.Add G col.Add h col.Add i col.Add j col.Add k col.Add l col.Add m...
  15. S

    Adding Items to a Collection

    Greetings, I would like to add a range of columns to a collection and then use the collection in a for each loop including each column-element in the collection in a procedure. like, Dim i As String For Each i in col Is there a more convenient syntax than something like col.add F I would...
  16. H

    Auto update secondary tab using formulas.. source is primary tab

    hi team , unable to attach sample file Have generated an excel to track yearly events that we are looking to host in the company for apac as a whole region...I wanted formulas on tab ‘charity 2018’ so that it auto populates data from ‘schedule’ sheet/tab. For example in charity 2018 tab, I...
  17. B

    validation list help

    I'm trying to get this in cell validation list to work but I'm not having any luck so far. this is what the code is meant to do... open the reference wkbk & go the reference sheet starting at col B row 2 add value of cell to ctcType collection & continue to down column B adding value of cells...
  18. T

    Formula to find values over 53 sheets in a workbook

    I have a multiple sheet workbook that has 7 dates on a row with values listed under those dates in up to 7 rows. I also have a collection sheet with a moth of dates in a column. I want to search all 53 sheets for a date on the collection worksheet. When a date is found, I need to copy the data...
  19. J

    Formatting a MsgBox to have two columns

    Good Afternoon! I have written the below code to work with a collection and then display the collection as a message box. The problem is that when the string name is to large it throws off all of my alignment. Any help you can give would be awesome. I just want a way to space out the strings so...
  20. K

    error handling in a collection

    I am looping through a collection and try to trap the error that occurs when the member of the collection does not exist. I can't get my error handling to deal with the situation, it just ignores my direction to go to a line number and crashes. On Error GoTo 32 If Not...

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
Back
Top