Tag: arrays

VBA Dynamic array : change size and use of Get Let Property

The problem: I would like to create a dynamic array that change size when an event occurs (Event Calculate), by preserving its content and extending by one its size. Let’s say I want to do this for a vector of double (and also a date type). The data is updating in a specific cell. My […]

Using excel function LINEST with multiple criteria

First question in the forum and i’m fairly new to vba so bear with me. I’m using LINEST in excel for regression coef and stats. The problem is that the function is very limited in the way you provide the arguments (y’s and x’s). The first thing I got in trouble was the need for […]

Adding data filter to excel index array formula

OK, so I have a “working” knowledge of excel formulas, but I’ve confused myself a bit with this one’s complexity. I have a sorting array in “Sheet 2” that works perfectly to grab unsorted data from column “E” in the sheet “Level 4”, sorts the data, drops any blanks and returns a list sorted by […]

How to Get Earliest Date Based on Criteria for Large Table?

I have a spreadsheet with ~550k rows. It has Data in first 3 columns, then a calculation field in the 4th like this: The 4th column is a calculation that finds the earliest date (in column A) based on the users name (column C). I am using this formula: =MIN(IF(C:C=C2,A:A)) Followed by pressing Ctrl+Shift+Enter rather […]

using a dictionary and array to count cell values

This is an extension of this question. I want to do something similar, but I am not very familiar with dictionary objects, and the code provided in the answer is very advanced, so I am having trouble understanding it. For instance, some of the syntax is not very clear, and variable names are not very […]

Excel 2007 Non Adjacent Array Index Match

In my work they use Excel 2007 and I have a calendar for planning Facebook’s posts for the month. I was asked to have at all times the Likes, Shares and Reach of each post, the most liked and less liked post. Since my cells are non adjacent, I’m having a hard time using the […]

Generate equidistant sequence of number in one cell using Vba

Example: A B C D Min Max Steps Result 10 70 4 10,30,50,70 30 150 5 30,60,150,90,120 40 100 2 40,100 50 150 3 50,100,150 60 160 6 60,80,140,100,120,160 120 160 3 120,140,160 I Want to generate Result in D2 which are “equidistant sequence of number” (such as “10,30,50,70”) based on Min in A2, Max […]

Merge cells into array in excel

I want to calculate this formula =SUMPRODUCT(N1:Q1;{L6;M6;O6;P6}) This is possible if {L6;M6;O6;P6} is an array. I’ve tried with this formula, but it is not working. =SUMPRODUCT(N1:Q1;CHOOSE({1;2;3;4};L6;M6;O6;P6)) It is not possible to change the structure of tables. Is there some workaround without using VBA? Thank you for quick response!

Public dinamic array

I’m trying to set public dynamic array like this: Public BenData(1 To Worksheets(“Database”).Range(“A9”).End(xlDown).Row, 1 To 4) But VBA doesn’t allow that. Is there any way around?

Taking out multiple values in VBA

I need to analyse 4 values. However, these 4 values change every time I run the code. I need to run the code 100 times and paste the 4 values in different cells. This is what I have come up with so far: Sub min() Dim i As Integer For i = 1 To 5 […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.