redim

Hi all, I am learning more about Arrays but I got to this point & I am stuck & need some help here. So in "Sheet1" I have a list of data that spreads for about 17k rows & over 9 columns & in "Sheet2" I have list of values that I type in column A which then I am looping against my data from...

Hi guys, I have a piece of code which requires using the FILTER Function, however the SourceArray argument is giving me a Type mismatch error, any help would be greatly appreciated. This doesn't work Dim bDistinct as Boolean Dim arrList2() Variant Redim arrList2(1 to 1559, 1) arrList2 =...

Greetings, I've been studying a user defined function that produces MLE's for independent variables, etc,etc. I am having trouble understanding how information generated in the array based function is presented to the user in a spreadsheet. Having run the full code a few times and having only...

Hi All, I have the following VBA script, which works for doing a series of somewhat random draws from a model, stores those values in an array, and then pastes the array into a sheet for graphing. Storing values from a single cell on each draw works fine, when I try to store an entire range of...

Hi, The code, below, is used to find a list of start and end dates from a matrix grid. I have been able to save the values in an array and output the results... however the results are weird. Each loop should output pairs of dates but its adding in lots of commas in the successive columns. I'm...

Hi In Gen Module Option Explicit Public curRec As Integer Public a$() Public b$() '------------------ in Userform1 Private Sub UserForm_Initialize() curRec = 1 ReDim a$(1 To 15) ReDim b$(1 To 15) With Sheets("Sheet1") Userform1.textBox1.Text = .Cells(2, 3).Value...

Hi there. I have two rows of data (x=time and y=diameter). There are a total of 30 rows of data and oftentimes there may be 2 or more identical values of the diameter for 2 or more days. I want to scan through the diameter data and output only the non-sequential-identical numbers. The code...

I have a spreadsheet that has data in rows 14 - 797, and columns A through AM. Column B has addresses. The next column of important information is in column D through AM. These columns layout like so: Column D = Landscape Charges, Column E = the z-score for those landscape charges, Column F =...

Hi I am trying to Redim a multi dimension array. I have declared the array at the top of the module as Dim DataStor as variant Then in the sub i have a loop For i = 1 to XYZ r = r +1 Redim Preserve DataStor(r,16) DataStor(r,1) = Unit DataStor(r,2) = Time etc Next i it picks up...

I'm using this to load a Listbox on a Userform with values where column H has 'Overdue' but the date is coming up in the US format, whereas I need it to be in UK format; With Me.ListBox1 .ColumnCount = 5 .ColumnWidths = "120,0,60,0,60" End With Set Rng = Range("D7:H57") ReDim Ray(1 To 5, 1 To...

Hi Sir, The following code plot the days between 2 dates into separate column. However, I need to get number of days between 2 dates monthwise; <tbody> ID S Date E Date 1 01/06/2017 15/07/2017 1 16/07/2017 12/08/2017 1 14/08/2017 30/08/2017 output should be ID...

Hi All, I have an array containing 45 items in a single column, and can't for the life of me see whats wrong with this: dim avarData() avarData = dic.keys k = UBound(avarData, 1) ReDim Preserve avarData(1 To k, 1 To 6) I want to add an additional 5 columns into my array and populate them...

I need help in modifying the appended Partial Correlation UDF to Semipartial Correlation UDF. A note on Semipartial Correlation (and Partial) can be found at http://www.listendata.com/2017/03/partial-correlation.html The Partial Correlation UDF is: Function pCorrel(R) Dim iRows As Integer...

Have many working functions returning one 2D array; wondering how I could return 2x 2D arrays? Example doesn't work; intended to show you what I'm trying to do Sub testFunction() Dim t As Variant Dim a As Variant Dim b As Variant Dim j() As Variant ReDim t(0 To 2, 0 To 2)...

Hi, I want to fill Tab pairs, like: [0] = Eng, 2 [1] = De, 3 [2] = PL, 4 [3] = FR, 5 etc... I know how to only add first value. How to 1) add second value and 2) check if the first element is into Tab? (because value like Eng, De....need to be unique into Tab) Dim CostCatTab() As Variant...

I have to use VBA to find the determinant ( =MDETERM ) of any size matrix/array. This is my code for a simple 2x2 matrix but don't know how to make it dynamic (for a 3x3 or 4x4 matrix). Any ideas? I tried using something like "Dim A(1 to n, 1 to n) as Integer" but keep getting an error...

Team, After this last upgrade Microsoft pushed this past weekend, many of my workbooks stopped working when I go from Redim vArray(0 to 0) to Redim Preserve vArray(1 to 1) - Apparently 1 is skipped. Simple Code 1 - Not working: Private Sub ArrayTest1() Dim vArray As Variant ReDim...

Hello. I partly created a code which tries to group data based on the values of an array. The problems are that I'm trying to extract data from multiple worksheets that belong to the same workbook so ReDim Preserve seems to fall out of range when the code reaches the 2nd worksheet. I'm not sure...

I have checked that the array is populated but the following code is failing to tranfer the array values to column A of the 'Category' sheet. Any advice welcome. For Each x In tmpArray Cells(Sheets("Category").Range("A2").End(xlDown).Row, 1).Value = x Next x thanks mark

Someone...I know someone...is bound to say it's bad practice to make a function declared as a particular variable type should remain that same variable type and is bad coding to do otherwise. However, I am curious if there is a way to do so... The scenario is this: I created a RegEx function...