collection

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

Hello, I confess I am relatively new to classes. I have declared an object variable at the top of module A thus: Public Teams As Collection The object is created in a procedure called "Create_Objects", also in module A: Set Teams = New Collection Without going into too much detail...