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

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

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

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

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

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

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?

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

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

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

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

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

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

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

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

This may very well be just too big, but here goes ... I've been handed a DARTS SCORE Sheet for a full LEAGUE The owners want to record each time all players score over 99 (>=100) The problem is multipartite: There are 6 players on the Team Each player can play in up to 3 "games" (TEAMS...

Hi all Id be grateful for some help with this problem I have a table of 50 rows, and each row is populated by a value which in turn has been generated by 5 vlookup formulas all adding the return values up. So for example B1 would by £740 being the sum of 700+10+10+10+10. Each of the "£700"...

Hi, I have a vba program with publicly declared variable arrays. My program loops around and when it does I need to clear the array and reset all the values to zero. What is the best way to do this? I notice that even when the program ends, the arrays still hold the values. Shouldn't the...

Hi all, I've been using Excel for decades but I've only just started using VBA. I currently have a task that entails copying 600 cells from several sheets in one workbook to a table in another. And then repeating the process on 300 other workbooks The code is quite simple Sheets("Section...

This is my code for checking the system clock and comparing it with the dates of the tax year Sub dateesystem() 'decclare all variable to differnt type '------------------------------------------------- Dim StartDateMonth01 As Date Dim StartDateMonth02 As Date Dim StartDateMonth03 As Date Dim...