collection

  1. 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...
  2. 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...
  3. 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.
  4. 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...
  5. 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?
  6. 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...
  7. 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...
  8. 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 =...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. A

    No event is triggered upon pressing tab

    Hello All, I have one userform on which I have a multi pahe. Each tab of multi page has 4 text boxes in column format . Each column having two text boxes. At the bottom of the user form, there are three command buttons (Next Page, Cancel and Close). All controls are added to a collection and...
  17. J

    Excel VBA events only working on last element of Collection

    Hello Everyone, I have a Multipage that is generating new pages with various CheckBox, ComboBox, TextBox and Button objects. Currently I'm just focusing on the CheckBox objects but the same code will applied for every different objects. I know that all 5 CheckBox objects are added to the...
  18. N

    how to get values in a collection of arrays in VBA

    Hi , can anyone help me with below query. i have a collection of arrays. and i need to read the values. eg: Dim resColl As New Collection resColl.Add Array(key, Key3("UserName"), Key3("Password")) in above add method i added arrays with 3 elements. and after adding i need to read the...
  19. S

    Opening PDF from VBA

    I am trying to use the following code to open a pdf file from a folder, but I keep getting the "File not found" error. Shell "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Adobe Acrobat DC.exe " & FullPath & """, vbMinimizedFocus" Is there something I am missing? Below if the whole...
  20. M

    Collection Item Attribute Change

    Hi, as I am new on this Forum, I don't know if my question has already been posted. If yes, please just show me which post. I have a collection with n classes (Batch) and I like to change attributes of the items. These attributes are strings like (Name, Adress, Phone, etc...) A create a GUI...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top