arrays

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

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