arrays

  1. L

    Clearing empties from a 2d array

    Hey guys, i have an array, once creating it i make it 2d: ReDim allPksWithEmpty(1 To lastRow, 1 To 2) Since I'm not able to set the 1st dimension anymore, i cap it out at maximum rows in sheet. Of course i only pick up a little data depending on some ifs, thus i end up with a big array...
  2. E

    How to import CSV data into multiple arrays and to return the multiple arrays via a function or sub in VBA?

    I want to create a VBA add-in that imports a CSV file, split the data columns into separate arrays and returns these multiple arrays to the sub that calls this add-in. Not sure what is the best way to go about this. For example, my data folder contains many CSV files where each has 5 columns of...
  3. N

    ARRAY number of rows and colums, how to know the correct values for the size...

    This is the first time I'm trying to use ARRAY's in Excel. Reference: =IF(ROWS(A$10:A10)>$D$8,"",INDEX(Gas!A$6:A$18,SMALL(IF((Gas!$A$6:$A$18>=$C$4)*(Gas!$A$6:$A$18<=$E$4),ROW(Gas!A$6:A$18)-ROW(Gas!$A$6)+1),ROWS(A$10:A10)))) For example, it seems that the reference table A6:A18 in this case...
  4. S

    out of memory with small array

    All, I've ran into an error with my code where it gives me an out of memory error after i try to paste an array to a range. I've read through a lot of posts and everything seems to be with arrays in very large sizes which mine only has 13,000 rows. The strangest part is i have some string...
  5. H

    Have the value in a cell cause the same value in an array of data to light up red when value is equal

    Hard to explain what I'm looking for or if it is even possible. B5 is the cell in which I have any value #.# (ex. 17.2) I have an array of data from J5 to AB5 which different values Another array from J10 to AB10 with different values and so forth Currently these arrays are not one on top of...
  6. L

    extending array per every input

    Hey guys, im trying to fill up my array and extended whenever needed: arraySizeX = 1 arraySizeY = 2 ReDim Preserve orderArray(1 To arraySizeX, 1 To arraySizeY) orderArray(1, 1) = "test" orderArray(1, 2) = lastRow - 1 arraySizeX = arraySizeX + 1 ReDim Preserve orderArray(1 To arraySizeX, 1 To...
  7. L

    union range into array

    Hey guys, I'm trying to dodge making cycles to fill up my array, so i was hoping to just do this: lastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row Set bigRange = Application.Union(Range(Cells(2, 3), Cells(lastRow, 3)), Range(Cells(2, 4), Cells(lastRow...
  8. B

    New to arrays - Array not returning values

    Hello all, I am trying to use arrays for the first time and I am flummoxed! Although the array seems to be populating when I write the values to it, any attempt to read those values fail as nothing is returned. Now I KNOW that this is my stupidity and I have checked and rechecked the code that...
  9. M

    Combined index & match formula

    Hello Mr XLers! This formula below only works when the date in my sales Tax Table matches the date in my materials date column. I need to be able lookup tax % with after 1/1/2019 which is the beginning date in my Sales Tax Table. Any help would be awesome! :confused::confused::confused...
  10. G

    Global array declaration

    99% of the questions on this group can be answered by anyone with zero VBA experience but with the willingness to spend a few of minutes googling around and trying out the code. This one is not. So please, I need help from someone who actually knows VBA, not newbies (like me) who need to get...
  11. D

    Replace ID Number with Customer Name using two arrays

    I am using an array to populate a ListBox. I understand how to create an array and use it to populate a listbox with items from the Jobs table. I also have a customer table. The Jobs table contains the CustID number. How would I approach replacing the CustID number with their name from the...
  12. W

    Load multiple data tables into arrays and using vlookup on the arrays for speed improvement

    I have a couple of hundred rows that I want to vlookup and I have over 100 data tabels, when I change a combobox a couple of thousand formulas recalculate. And it takes a couple of seconds to recalculate and I am looking at ways to increase speed. Will it be faster do load every data table...
  13. F

    Dynamic Share Array Issue

    I've got an issue with how to pull out the sum product of a value (X) set based on the share of presence of A and B respectively. The main problem is that I can't actually see A and B explicitly as shown below. Instead I've got a reference that tells me that A runs from 11/4 to 12/30 and B runs...
  14. S

    Why is the macro window opening when I try to pass an array from one sub to another?

    OK, from all I've read passing an array from one sub to another is easy, but when I try to do it here the VBA Macro window pops open and my code stops. I have tried to read everything else out there that tries to address this but no amount of defining or deleting of `()` helps. Sub...
  15. M

    Operation on a concatenated array

    Dear all, I would like to know if there is a way to use functions on concatenated arrays? I have 2 ranges of cells in the Name Manager as follows: Array 1 =Sheet1!$A$1:$A$5,Sheet1!$A$7:$A$10,Sheet1!$A$12:$A$16 Array 2 =Sheet1!$B$1:$B$5,Sheet1!$B$7:$B$10,Sheet1!$B$12:$B$16 When I try to...
  16. T

    Formula that can find result and include hyperlinks

    Hello, I been trying to figure this one out but I can't find a way to do it without using macros. In the "warranty" section, I want to be able to match the mode value with the list on column G. The difference is that the list will have hyperlinks to a downloadable format. Some of the values...
  17. 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...
  18. A

    Help Reqired on VBA Count Ifs Foumula using multiple Criterias

    Hi All, Please anyone help me on below request. I'm working on Automation Dashboard Please help on below Code PositiveScr = WorksheetFunction.CountIfs(Sheets("CSAT Data").Range("F:F"), "9", "10", Sheets("CSAT Data").Range("H:H"), "AMER", "EMEAR", Sheets("CSAT Data").Range("E:E") ">=1/12/2019"...
  19. N

    Cloning Custom Object from Array - Excel Crashes

    Hello everyone. I have some custom defined VBA classes and no idea why they keep causing Excel to crash. My classes define objects. I use instances of those objects in dynamic arrays. I can't use collections because I need to edit the objects in the arrays and swap out the entries in the...
  20. D

    Non sequential read and write with arrays

    I am attempting to pull data into 3 arrays. 2 arrays from sheet one, "Summary", and 1 arrays from sheet two, "Detail". After pulling the arrays, I will compare Array1, "Project Name" on sheet Summary to Array3, "Project Name" from the detail Sheet. If the values match, I need to take he...

Some videos you may like

This Week's Hot Topics

Top