1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. T


    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...
  8. 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.
  9. L


    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...
  10. 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?
  11. 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...
  12. 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...
  13. 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 =...
  14. 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...
  15. 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...
  16. A

    Nested Dictionary?

    I have a set of data: Domains; Users; Machines I have a task that requires me to check to see if a domain user has logged into a particular machine in the past. There are unlimited domains, an undefined number of users, and the machine name could potentially change at any time. The tool has to...
  17. C

    Copy multiple rows

    Im looping a range of cells in column A. If it meets a certain criteria need to copy that whole row. Instead of individually copy and pasting the row everytime i loop when the if statement is true i want to keep adding that row to an array, collection or whatever is a better solution and once...
  18. L

    thisworkbook and activebook access without usign workbooks collection

    Hi The code below is going to run no problem to my surprise. It will also run without typing Application. Now I am confused. I know to access a workbook I need to go through the workbook collection like the following msgbox(application.workbooks(1).name) So why the code below is OK? Thank...
  19. T

    Inspecting elements of a collection

    This code checks the version of Excel installed: Option Explicit Sub Test() Dim abc As Collection Set abc = New Collection abc.Add Module2.Version abc.Item (0) ' FAILS HERE End Sub Option Explicit Public Function Version() As Collection...
  20. 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...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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