arrays

Hi all Im trying to figure out how i can set the max value of an array from a value obtained from another source, like the number of rows in a range for example Public Sub test() 'Const x = 12 Range("e12").Select nld = Range(Selection, Selection.End(xlUp)).Rows.Count y = nld Const x = y...

I'm toying around with using arrays instead of working in ranges. Here is a simple array Sub dotest() Dim DirArray As Variant starttime = Timer DirArray = Range("c:t").Value For Each x In DirArray 'Debug.Print x Next MsgBox Round(Timer - starttime, 2) End Sub While I want to load the entire...

Hi Forgive me if this has been asked before but none of the suggestions on the net seem to work for me. I need to be able to copy a number of multiple array formulas in one workbook and paste them into another (different) workbook but without excel adding or changing any references contained...

I read that purely for speed purposes, you should use a For Each loop when looping through a collection. For arrays, use a For Next loop. What about for a dictionary? Which is faster? Thanks

Hello Could someone please explain how to count a sumproduct of two arrays? I need to count the number of cells that are not 0 after calculating a sumproduct. The COUNTIFS formula below (see screenshot) does not work. #VALUE ! is returned. It is just a testing sample. Of course, I would...

Hi, I have some code to output arrays. But the problem is that when the column changes I need to update all the code. My question - is there some way to re-write this so that I can use offset and only update a single line of code? Range("BO39:BO116").Value = Application.Transpose(aa)...

Hi all, I am always confused as to what Excel is doing when putting array arguments into an IF function. for example, if i have the following 2 arrays: Array A: 1, 2 3, 4 5, 6 Array B: I, II, III ref cell <--to input a number for comparison and if I have the following formula: =IF ( ref cell...

Hi I hope someone can help me... I have a 2 part query regarding MATCH INDEX and SMALL formulas. I have been trying to create a personnel database that returns peoples names that have experience in specific nominated sectors. Query Part 1: I have managed to identify the row number of a...

Array 1 = {"DX";"DZ";"WX";"TX";0;0;0;0;0} Array 2 = {"BX";"CX";"DS";"EX";"FX";"HX";"IX";"KX";"LK";"N";"NX";"OP";"SK";"SX";"TX";"WX";"ZX";0;0;0} How do I make an array that combines elements common to both arrays that aren't 0? I'd expect {"WX";"TX"} as a result.

Hi Guys - Can someone help me come up with a formula that will be the equivalent of the below but without the need for arrays (Ctrl-shift-enter)? =SUMPRODUCT((Expenses!$R$10:$R$26=$O19)*(Expenses!$T$5:$BG$5=$B$3)*(Expenses!$T$7:$BG$7=B$6)*Expenses!$T$8:$BG$26) Thanks! Alyssa

Hello - I made a jenky macro that transposes a large set of data. It runs slow because it's a lot of data that it goes through. So I had the bright idea that I wanted to spend 2 days in a row improving this dumb macro I made. I really tried making this an array or a range type macro and keep...

Hello, I am here because after an exhaustive search of the internet, I simply cannot find a solution to an Excel problem I have. Here's the situation. I have my own accounting spreadsheet that I have created myself. I have separate sheets for a lot of things, but two of them are income and...

Hi guys, First time posting, and hopefully an easy solution. I have a table called "Workbank" which is 33 columns, but will have a variable number of rows. One of the columns is comprised of alphanumeric strings such as: aa-###, aaa-##, aaaa-#. I would like to store these column values to a...

I have a spreadsheet with multiple part numbers and in many cases, duplicate part numbers. There are 10,000 rows! <tbody> F G AO 1 000746-A-1 REV H <tbody> 000746-A-1 REV H OP-10 </tbody> <tbody> SHEAR 1st PC FOR INSPECTION Use 14 Gage (.075") 304SS, Shear strip to 3.25"Wide Move to...

All, I have a sum products that is working for individual things. =SUMPRODUCT(--(MONTH($B$15:$B$1010)=MONTH($AF$3)),--(YEAR($B$15:$B$1010)=YEAR($AF$2)),X15:X1010) I then write one with mult arrays...

I am trying to accomplish in Excel following case but I am not able to make it work. Could you please help? I have got given volume data for asseblies (as example Ive choosed bikes) In second part of table I have number of parts needed for each assembly The task is to calculate quantity...

Hello guys, I've been trying to get rid of several Index- Small - If arrays since I'm pulling data of over 100,000 rows and this causes excel to pretty much slowdown and process forever. I've spent over 2 full days trying to figure this out to no avail. This is pretty much advanced excel, so...

I'm having a problem with getting arrays populated with functions that are called from a dll. A quick example is: Private Declare Function MHL200_GetDLLVersion Lib "dll path" (ByRef pulDllVersion() As Long) As MHL200_ErrorEnum Then, I call it as such: Dim ulaVersion(2) As Long...

https://imgur.com/sD0lKf3 Hi Everyone, I'm fairly new to VBA and i'm trying to achieve the above task. Basically i want to sort through the data such that the animals are summed to provide a total sum of a particular type of animal under a designated letter as shown. also, I only want to...

Hi guys, I want to obtain the sumproduct of the following two arrays, essentially 1 x 0.2 + 2 x 0.1 + 3 x 0.3. A 1 B 2 C 3 B 0.1 A 0.2 C 0.3 SUMPRODUCT would work neatly if the first column in both these arrays were identically ordered (i.e. ABC and ABC). But because they're not so, it...