array formula in vba

I have 5 tabs of data which have varying numbers of columns and varying numbers of rows. The data that gets input into these tabs will be regularly changed, and the number of rows and columns with each input will be an unknown variable. The tabs of data look like this (highly simplified for...

I recently stumbled across array formulas (specifically LINEST)which could be a big help if I can learn how to use it properly. While I can set-up a 2x5 cell region for theresults, I only need and slope and intercept. If I turn the Stat option to False this is exactly what I get and need(which...

Hi All ! How can I make an "array formula" in VBA without "using" an actual "excel fomula" I'm asking because I need the VBA to work regardless of the language of the Excel or of the environment. so although "=SUM(DATEVALUE(A1:A555))" works, (and would work if I used it via VBA in an english...

I have a worksheet that populates with some amount of dates in col A and account codes in row 1. I then have a range with is from B3:(Last Col, Last Row) called MyRange. I now want to perform an index match array formula in each cell in my range. (The results of the index match can be just the...

This piece of code is not working properly as the first value loads into the array but no other values load. I can see that "i" increments to 2 but as soon as it hits the line with "For i = 1 To UBound(aCoupeColAd)" it reverts back to 1 again. Here is the code if anyone can help work this out...

Hello, I need the below code to count the number of columns in a worksheet and extend an array formula to look at all of those columns. I know there is a 255 character limit with arrays so I've tried to break it in to smaller chunks. I'm getting a 'Type mismatch' error and it's breaking at the...

Hi, I want to match data from sheet1 with data in sheet2(Reporting matrix) using array formula in order to count the data. I used below macro that doesn't work. can any one help me to complet this. My thanks in advance! Sub Count() Dim Row As Integer, Col As Integer Dim x As Integer Dim y...

I'm having trouble getting the array formula below to return the values I'm expecting. It's an implementation of the normal equation for solving linear regression problems using linear algebra. The expected output is an array with the same number of elements as there are columns in X (the...

Hello Guys, I got the following array formula given to me on this forum: {=IF(A7 < > A$5,"",$A$5&" "&TEXT(MIN(IF('O:\Operations Supervisor\`Rosters 2011\[Leave allocation 2011 Master.xls]Leave Approved'!$I$15:$I$215=$E7,IF('O:\Operations Supervisor\`Rosters 2011\[Leave allocation 2011...