Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Populate an Array with a Discontiguous Values
Hi,
Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array with a Discontiguous values. For example, instead of fully declaring a range like this Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range and then use something like a loop to populate the array e.g. Dim myArRanges(1 to 3, 7 to 9) Redim MyArray(myArRanges) Could anyone suggest how this might be done. Thanks ...pc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Populate an Array with a Discontiguous Values
I guess you could do this...
Dim MyArray() MyArray = Array(1, 2, 3, , , , 7, 8, 9) If MyArray is a Variant array as you have shown, elements 4, 5 and 6 will be "missing" in the sense that no value is assigned to them at all... they are not empty or null, they just don't have anything assigned to them. You will get an error if you try to address them although you can test for that using the IsError function. If you declare your array with an actual type (Integer, Long, String, etc.), then elements 4, 5, and 6 will take on the default value for those data types (0, 0, "", etc.). Rick "PMC1" wrote in message ... Hi, Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array with a Discontiguous values. For example, instead of fully declaring a range like this Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range and then use something like a loop to populate the array e.g. Dim myArRanges(1 to 3, 7 to 9) Redim MyArray(myArRanges) Could anyone suggest how this might be done. Thanks ..pc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Populate an Array with a Discontiguous Values
You can loop thru cells and test for a condition, for example, the following
untested code only adds data to the array if the cell is not blank: Dim rCell as Range Dim lX as long Dim vArray() as Variant Dim sMSG 'Load the array: For Each rCell in ActiveSheet.UsedRange.Columns(1).Rows If Trim(rCell.Formular1c1) < "" Then '<<< filter out blank cells lX = lX + 1 ReDim Preserve vArray(lX) vArray(lX) = rcell.Value End If Next rCell 'now check the contents: For lX = 1 to Ubound(vArray) sMsg = sMsg & vArray(lX) & vbCr Next lX Msgbox sMsg HTH "PMC1" wrote: Hi, Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array with a Discontiguous values. For example, instead of fully declaring a range like this Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range and then use something like a loop to populate the array e.g. Dim myArRanges(1 to 3, 7 to 9) Redim MyArray(myArRanges) Could anyone suggest how this might be done. Thanks ...pc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Populate an Array with a Discontiguous Values
Rick Rothstein (MVP - VB) wrote:
I guess you could do this... Dim MyArray() MyArray = Array(1, 2, 3, , , , 7, 8, 9) . . . If you declare your array with an actual type (Integer, Long, String, etc.), then elements 4, 5, and 6 will take on the default value for those data types (0, 0, "", etc.). Rick No. You will get a Type mismatch error. Alan Beban "PMC1" wrote in message ... Hi, Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array with a Discontiguous values. For example, instead of fully declaring a range like this Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range and then use something like a loop to populate the array e.g. Dim myArRanges(1 to 3, 7 to 9) Redim MyArray(myArRanges) Could anyone suggest how this might be done. Thanks ..pc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Populate an Array with a Discontiguous Values
PMC1 wrote:
Hi, Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array with a Discontiguous values. For example, instead of fully declaring a range like this Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range and then use something like a loop to populate the array e.g. Dim myArRanges(1 to 3, 7 to 9) Redim MyArray(myArRanges) Could anyone suggest how this might be done. Thanks ..pc I'm not 100% sure I know what you are asking, but if the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, then with 1,2,3,4,5,6,7,8,9 in A1 to A9, The following will generate a 1-D array of six elements: 1,2,3,7,8,9 Dim rng As Range Dim MyArray() As Integer 'or Variant or Long or Single or Double or 'Byte Set rng = Range("a11:a13,a17:a19") Assign rng, MyArray Alan Beban |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Populate an Array with a Discontiguous Values
Thanks for catching that Alan.
<<Note to self: Test **before** you post! Rick "Alan Beban" wrote in message ... Rick Rothstein (MVP - VB) wrote: I guess you could do this... Dim MyArray() MyArray = Array(1, 2, 3, , , , 7, 8, 9) . . . If you declare your array with an actual type (Integer, Long, String, etc.), then elements 4, 5, and 6 will take on the default value for those data types (0, 0, "", etc.). Rick No. You will get a Type mismatch error. Alan Beban "PMC1" wrote in message ... Hi, Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array with a Discontiguous values. For example, instead of fully declaring a range like this Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range and then use something like a loop to populate the array e.g. Dim myArRanges(1 to 3, 7 to 9) Redim MyArray(myArRanges) Could anyone suggest how this might be done. Thanks ..pc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate an array that is a subset of a larger array? | Excel Worksheet Functions | |||
Populate an array using a formula | Excel Programming | |||
Populate Array | Excel Programming | |||
populate array | Excel Programming | |||
Populate array | Excel Programming |