arrays

  1. S

    Stability issues with complex array UDF calls

    I have a workbook with a rather complex set of vba UDFs used to perform thermodynamic flash calculations. Multiple vba routines are called for each udf call. There may be +100 individual UDF calls within a work book across multiple sheets. Many of these will be interlinked with functional...
  2. M

    VBA: How to merge six seperate 10x1 arrays into one 10x6 array?

    I'm working on a linear regression sub that creates a model equation to predict data points and I've reached a standstill in my project as I cant seem to find a pathway to merging the six 10x1 arrays into a single 10x6 array in order to perform a series of matrix manipulations using the 10x6...
  3. C

    Median Function within VBA, which accesses an array within the function, not a cell range on the worksheet

    I have read the threads about array formulas and I have tried that approach to my problem but it is not working for me. Even doing CSE on each cell and then F9 when data is changed is not working, so I gave up on that, and am now writing my own VBA. I have the VBA function (Median_No_Zeroes)...
  4. A

    VBA Trying to write new array from another array

    I'm trying to create a new array from a larger array that has 4087 rows and 19 columns. I cycle through the main array to count the number of records matching the year 2017 of which there are 714. So my new array will be 714 rows by 11 columns. Most of the undernoted code works including the...
  5. R

    Position(s) of the Large after multiplication

    <tbody> </tbody> In my example I've got components of 5 combinations in B5:C9 to be multiplied, Extra is added to each of the B5:B9 and C5:C9 array(s) when calculating the Largest product of those arrays. Instead of listing the results of the multiplication I'd like their...
  6. N

    Userform data arrays

    Hello Excel Friends,<br> <br> I have a user form that currently publishes the data to a database sheet in one row, however, what I need it to do is publish the data vertically instead of horizontally, but I need 2 of the form sources to be replicated in each row.<br> <br> My current user form...
  7. S

    Unable to get the correl property of the worksheet function class

    I am having trouble with the line below DistanceMeasure = (1 - Application.WorksheetFunction.Correl(Application.WorksheetFunction.Index(X, 0, 2), Application.WorksheetFunction.Index(TempSample, 0, 2))) * 100 X and TempSample are both arrays with an equal number of elements. Am I using...
  8. adrienne0914

    SUMPRODUCT alternative

    I have a workbook with 2000+ lines and 111 columns. As a result, SUMPRODUCT takes forever. My formula on Summary tab is: =SUMPRODUCT((Source!$C$33:$C$2792=$A2)*(Source!$E$33:$E$2792=$C2)*(Source!$H$32:$DH$32=D$1)*Source!$H$33:$DH$2792) I tried SUMIFS but got a #VALUE error because arrays are...
  9. S

    2 Dimensional Arrays VBA

    I would like to be able to loop through arrays to do a test. I have one big array. It is two dimensional and has 2000 objects. From this array I would like to test every consecutive set of 20 objects to compare to another array. What kind of structure am I looking for here? In other words...
  10. M

    Single Cell Array Question!

    Assume the following: Cell A1 = 20 Cell A2 = 35 Cell A3 = 45 =SUM(A1:A3) = 100 But I want to have those 3 values within one single cell as an array like A4 ={"20";"35";"45"} and do = SUM(A4). However, when I do this, the result = 20. When I read the cell value of A4 with VBA, Range(A4).Value...
  11. P

    How can I dynamically increase the number of rows in an array when new data is available to assign to the array

    I am using the VBA code below to copy elements from a semi-structured workbook to an array and then from the array into a target worksheet of another workbook. My problem is that the macro takes a long time to run and (sometimes) causes Excel to freeze. My approach is to define an empty 2D...
  12. A

    Ebook

    I just attended a session where a free ebook was mentioned on dynamic arrays. I was given an incomplete link. Can I get the link?
  13. MrExcel

    Dynamic Arrays Debut September 24

    Microsoft just announced Dynamic Arrays for Excel. They say that Ctrl+Shift+Enter will not have to be used anymore. It is the 4th item on this page: https://www.microsoft.com/en-us/microsoft-365/blog/2018/09/24/bringing-ai-to-excel-4-new-features-announced-today-at-ignite/ but I think it is...
  14. G

    Using an Array formula with find text and find number start.

    Hi guys, I'm working with a data set that I need to do some sumif type stuff with qualifications both on the rows and columns. This wonderful sight got me started with arrays, and I've gotten partway to my solution with an array. The below formula gets me part of the way, but now I'd like to...
  15. K

    Issue Averaging Array bc of Div/0 errors & 0's

    I am getting an error at the worksheetfunction.average line when I run this macro because of div/0 errors in the data. It runs fine when there are no div/0 errors in the code, so everything else is fine. I just need a way to be able to ignore the div/0 in the data, and preferably to filter out...
  16. B

    How to check if value of cell is a part of an array

    Hello, I need some help again. I'm working with thousands of lines of data in Excel and I need to check if any value in a specified column is one of the values from a specified set of values. I was reading a bit about arrays and thought that they may be useful here, but I never used them before...
  17. M

    Copying Non-contiguous columns from one workbook and pasting them into a Table in another workbook

    Hello all I am a first-time poster to this forum, but have read through a lot of the posts and have gained so much knowledge to help in my own journey as a relative novice in learning and applying VBA in my job as an analyst. I have a question as to how to paste certain non-contiguous columns...
  18. M

    transform array of varying lengths

    Hi, I have been fiddling with this logic, but I can't seem to make it work. Basically I just want to transpose arrays with varying lengths using the Ubound property. Here is the original code: Set rng = Range("VBAOutput") rng.offset(0, 229).Value = Application.Transpose(array) THANKS
  19. C

    Formula to find Mis-Matches in two arrays of varying size

    Good afternoon, I am trying to figure out a formula that returns true/false on two array saying if they match. A simple example of what I want is as follows: Array1: {"A", "C", "D"} Array2: {"A", "B", "C", "D", "E"} Desired result: {True, False, True, True, False} Using ={"A", "C"...
  20. C

    find partial match of string between two arrays

    I have two arrays. Array A has a list of multiple terms in a column such as this: <tbody> Urinary Bladder Cancer|Bladder Tumors|Transitional Cell Carcinoma of the Bladder|Malignant Melanoma|Melanoma|Skin Cancer|Carcinoma, Non-Small-Cell Lung|Lung Cancer Platinum Sensitive Ovarian...

Some videos you may like

This Week's Hot Topics

Top