Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range.value equal to /part/ of a (3D) variant array?
I sometimes pull ranges into an array to save time (instead of looping).
Right now, I'd like to do the opposite- I have a complicated workbook and the code isn't working properly, so I need to see the array during processing (refreshed during a larger loop) to see what is going on. The problem is that while I can set a worksheet range equal to a 2D array, this particular array is 3D and there is only one slice of that I need to see (effectively a 2D slice) So what do I need to change in my syntax to go from the two dimensional assignment: Sheet1.range("A1:J100).value = TestArray to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and I only need to see where the third parameter =2; Sheet1.range("A1:AD100").value = TestArray ([all],[all],2) Is this possible, and if so, how do I reference TestArray to get my desired 'slice'? Thanks for any assistance, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range.value equal to /part/ of a (3D) variant array?
To clarify, here is some test code I was playing with; I haven't found any
syntax for the last line that will work. Sub test() Dim TestArray As Variant ReDim TestArray(1 To 3, 1 To 3, 1 To 3) For x = 1 To 3 For y = 1 To 3 useY = Chr(y + 64) For z = 1 To 3 useZ = Chr(z + 32) TestArray(x, y, z) = x & useY & useZ Next Next Next Sheet7.Range("A1:C3").Value = TestArray(, , 1) 'can't get this to work with any syntax I've tried End Sub "ker_01" wrote in message ... I sometimes pull ranges into an array to save time (instead of looping). Right now, I'd like to do the opposite- I have a complicated workbook and the code isn't working properly, so I need to see the array during processing (refreshed during a larger loop) to see what is going on. The problem is that while I can set a worksheet range equal to a 2D array, this particular array is 3D and there is only one slice of that I need to see (effectively a 2D slice) So what do I need to change in my syntax to go from the two dimensional assignment: Sheet1.range("A1:J100).value = TestArray to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and I only need to see where the third parameter =2; Sheet1.range("A1:AD100").value = TestArray ([all],[all],2) Is this possible, and if so, how do I reference TestArray to get my desired 'slice'? Thanks for any assistance, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range.value equal to /part/ of a (3D) variant array?
On 15 Jul, 20:39, "ker_01" wrote:
To clarify, here is some test code I was playing with; I haven't found any syntax for the last line that will work. Sub test() Dim TestArray As Variant ReDim TestArray(1 To 3, 1 To 3, 1 To 3) For x = 1 To 3 * * For y = 1 To 3 * * * * useY = Chr(y + 64) * * * * For z = 1 To 3 * * * * * * useZ = Chr(z + 32) * * * * * * TestArray(x, y, z) = x & useY & useZ * * * * Next * * Next Next Sheet7.Range("A1:C3").Value = TestArray(, , 1) *'can't get this to work with any syntax I've tried End Sub "ker_01" wrote in message ... I sometimes pull ranges into an array to save time (instead of looping). Right now, I'd like to do the opposite- I have a complicated workbook and the code isn't working properly, so I need to see the array during processing (refreshed during a larger loop) to see what is going on. The problem is that while I can set a worksheet range equal to a 2D array, this particular array is 3D and there is only one slice of that I need to see (effectively a 2D slice) So what do I need to change in my syntax to go from the two dimensional assignment: Sheet1.range("A1:J100).value = TestArray to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and I only need to see where the third parameter =2; Sheet1.range("A1:AD100").value = TestArray ([all],[all],2) Is this possible, and if so, how do I reference TestArray to get my desired 'slice'? Thanks for any assistance, Keith- Hide quoted text - - Show quoted text - phillip London UK This is one way Sub test() Dim TestArray As Variant Dim result As Variant Dim slice As Long slice = 1 'or 2 or 3 ReDim result(1 To 3, 1 To 3) ReDim TestArray(1 To 3, 1 To 3, 1 To 3) For x = 1 To 3 For y = 1 To 3 usey = Chr(y + 64) For z = 1 To 3 useZ = Chr(z + 32) TestArray(x, y, z) = x & usey & useZ Next Next Next For j = 1 To 3 For k = 1 To 3 result(k, j) = TestArray(k, j, slice) Next Next Sheet1.Range("a1:C3").Value = result End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting a range.value equal to /part/ of a (3D) variant array?
Anyone?
Thanks! Keith "ker_01" wrote in message ... To clarify, here is some test code I was playing with; I haven't found any syntax for the last line that will work. Option Base 1 Sub test() 'set up a sample array for testing Dim TestArray As Variant ReDim TestArray(1 To 3, 1 To 3, 1 To 3) For x = 1 To 3 'to fill dimension 1 of array with 1,2,3 For y = 1 To 3 useY = Chr(y + 64) 'to fill dimension 2 of array with A,B,C For z = 1 To 3 useZ = Chr(z + 32) 'to fill dimension 3 of array with !,",# TestArray(x, y, z) = x & useY & useZ 'should result in (1,A,!); (1,A,"); (1,A,#); (1,B.!); etc... to (3,C,#) Next Next Next '*** paste a slice of the sample array into a worksheet- this is the key line that I need help figuring out *** 'because I need to paste a 2D section of a 3D array Sheet7.Range("A1:C3").Value = TestArray(, , 1) ' where the "1" should limit the pasted range to everything that ends with "!" End Sub "ker_01" wrote in message ... I sometimes pull ranges into an array to save time (instead of looping). Right now, I'd like to do the opposite- I have a complicated workbook and the code isn't working properly, so I need to see the array during processing (refreshed during a larger loop) to see what is going on. The problem is that while I can set a worksheet range equal to a 2D array, this particular array is 3D and there is only one slice of that I need to see (effectively a 2D slice) So what do I need to change in my syntax to go from the two dimensional assignment: Sheet1.range("A1:J100).value = TestArray to a slice of a 3D range, where TestArray(1 to 30, 1 to 100, 1 to 3) and I only need to see where the third parameter =2; Sheet1.range("A1:AD100").value = TestArray ([all],[all],2) Is this possible, and if so, how do I reference TestArray to get my desired 'slice'? Thanks for any assistance, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variant array with formula strings to range formulae | Excel Programming | |||
for each in range gives variant array | Excel Programming | |||
Write from variant array into range | Excel Programming | |||
Runtime error 13-- Setting a variant Range?! | Excel Programming | |||
Best way to paste a variant array into a range? | Excel Programming |