collection

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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?

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

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

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

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

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