redim

  1. M

    VBA Copy data from Array to another help

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

    VBA FILTER function: SourceArray argument causing Type Mismatch error?

    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 =...
  3. S

    User Defined Functions: working with function output

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

    VBA array resizing for cell range covering 10 cells

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

    Help with clearing a ReDim Preserve Array

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

    How to get the respective data from 15 Cells of each row in a praticular column of sheet by pressing next button in User form in respective textboxes

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

    Excel Arrays - Making new ranges by deleting sequential duplicates from old ranges

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

    VBA Macro - Search and Return Help

    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 =...
  9. T

    Redim Question

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

    Listbox date problem

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

    Tweak existing code to get the desired output

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

    VBA Redim Preserve subscript out of range

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

    Semipartial Correlation UDF

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

    function to return two 2D arrays

    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)...
  15. F

    Unique Values into Arrays

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

    Dynamic Array in VBA

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

    Redim Preserve Mystery - Excel Can't Redim Preserve array with 1

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

    Subscript our of range with ReDim Preserve and UBound

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

    array values to worksheet

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

    VBA: REDIM Function?

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

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top