collection

  1. C

    Retrieving specific cells via index on a Row

    Hi all, Silly question but this is so unintuitive for me: For Each Table in Doc.tables For Cnt=1 to Table.Rows.Count Row = Table.Rows(Cnt) If ... DataRow = Table.Rows(Cnt+1) MsgBox DataRow.Cells(2) End If Next Cnt Next Table I can do...
  2. M

    Preserve data validation input messages when dragging

    I want to be able to retain data validation input messages when dragging the value of one dv cell to another with a different input message. To do this I'm trying to establish a collection of input messages before any edits to the sheet are made so that when I drag a dv cell's value to another a...
  3. N

    Managing Payment table separate from billing, calculating lps on every received payment based on criteria.

    Current Scenarios: My office colleagues are recording account receivable and sales data in sheet4, however, some companies have started multiple payments during the month and it is now difficult to manage them in this format. Action: so I decided to make new sheet for recording payments, which...
  4. G

    Is there a way to call array member by key instead of index?

    I have been using an awkward method to store and call my UDT arrays by key. That is by making an array and corresponding collection for storing the array index and calling the content by key, please see my code below. Is there better practice for this in VBA? That maybe can call array member by...
  5. G

    Populate multiple Variant arrays with loop and collection

    Hi folks, I am trying to figure out how I can use a loop to 1) establish a number of arrays in a collection and 2) populate those individual arrays inside the loop. Code I am trying to figure out below and a sample mini-sheet at the bottom. Sub MakeAndFillArrays() '######### 'Code should make...
  6. M

    VBA Macro to Loop though CheckBox Values

    Hi Folks, I am starting with an example found here to dynamically create checkboxes on a user form - with a Class Module for events. The example shows the value of each checkbox in a message box when it is clicked on the form. I am trying to loop through the entire collection of checkboxes...
  7. G

    RANGEs into COLLECTION: deleting them in loop, cause row ranges move to left?

    Hello I have a strange Problem: if I loop through a collection, where I put Range into it, and delete them with VBA, the last row somehow "grabs" a range from "right" and pull it to the left. Please see the pictures: Procedure_1: here you see the spreadsheet Procedure_2: This is my Listbox1...
  8. M

    Function Not Working - Sort & Remove Duplicates by Creating New Array

    I am not well versed in VBA and have no idea where to start finding the issue here. I need to write a function that will take a 1 dimensional array as an input and spit out a new array with all the values sorted alphabetically and duplicates removed. This input "SourceList" column has text...
  9. K

    (VBA) Find rows that meet criteria and perform calculation

    Hello there, I have a list of companies with corresponding revenue in 2019 and 2020. There are some companies with 0 revenue, and some with blank cell. My goal is to use VBA to identify all the companies with revenue > 0 in 2019 or/and 2020. And then compare the revenue in both years. Then...
  10. J

    Compiling and accessing nested collection within a nested dictionary

    Hi, First time posting a thread, long time user/reader of threads. I need help solving a problem that I can't find the answer (or close to the answer) to. What I want to do: Given a table of data which includes Part number, quote date, quantity, price and supplier, I want iterate through the...
  11. C

    Writing collection to sheet seems slow.

    Hi, I have a 2 dimension collection. The collections has 7 rows and 25 columns. Writing this collection to a sheet takes about 60 seconds, which is slow to me. The calculations are set to manual, and the displays, events and screenupdaing are set to false. What else could be causing the...
  12. T

    Collections

    In this code: Dim Rng As Range Set Rng = Sheet1.Range("A1:D4") Dim RngElement As Range For Each RngElement In Rng RngElement.Value = 100 Next RngElement it populates every cell in the range A1 to D4 with the value of 100. I was of the understanding that the variable RngElement...
  13. L

    worksheets the collection

    Hi The + sign in the sheet tab that is used to add a new sheet, is it the Worksheets "Collection" object in VBA? Thank you.
  14. L

    workbooks.close

    Hi I am trying to use workbooks.close (the close function of the collection workbooks). So I wrote the code below. but that code will close the workbook i am working on. I wonder if I can close other workbooks but not the one I am on right now using workbooks (collection)? I know I can close...
  15. R

    Adding to a collection (item and key) through named ranges

    Hi there, I have two columns of data that I want to add as an item/key to a collection. I have the two columns individually named but I can change that if necessary (i.e. two columns under one name). What's the best way to get these data into a collection?
  16. A

    Formulas required

    i have attached an excel work sheet. It is about a building TOWER having 6 wings ABCDEF having each 22 floors . they have expenses and collection you have prepare some sheets on same file indicating 1)LIST OUT FLATS POSSESSION NOT YET DONE MEANS NOT SOLD. 2) FIND OUT FLAT WISE EXPENSE...
  17. C

    Help Setting Chart XValues to a Collection

    I have written some code that cycles through a series of data and adds values that meet certain criteria to various collections. I'm now wanting to change the X & Y values and Data Labels of my chart to reflect those in the collections, but am unsure how to do this. I've previously only used...
  18. T

    Vlookup range as a collection

    In a vlookup, can the range be a collection? Say I have only two values in cells A1 and A2. If the range is an array, it works: Dim MyArray() As Variant MyArray() = Cells(1, 1).CurrentRegion.Value Dim g As Integer g = 2 Dim j As Variant j =...
  19. Sharid

    Email Extracting

    My code extracts emails from a website, this bit is fine It can also extract URLs from a site, the PROBLEM is that it extracts all LINKS and not just the domain name Also I can only extract one or the other, either emails on their own or Links, when what I want is for it to be something like...
  20. N

    For Each looping through own collection class

    Hello there. Most puzzling. The following worked first time I tried it, now it fails on the highlighted line with Object does not support this property or method Simple test Hotel class: Option Explicit Private pName As String Public Property Get Name() As String Name = pName End...

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