Hello Amelynn
Welcome to ExcelFox , the thinking man’s Excel forum 
Originally Posted by
Amelynn
Who can help me, ....
I think, therefore I am , and able to help you.
Originally Posted by
Amelynn
...... I don't understand how the line ".List
= Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
" works and I can't stay So....
To Explain
Question: _(i) What is Array(1, 3) ?
Answer_ It is like –
___1 _3
( Sometime we write in excel spreadsheet convention {1, 3} , but this usually means the same thing )
Question: _(ii)a) Evaluate("ROW(1:" & rng.Rows.Count & ")")
Answer:
Originally Posted by
Amelynn
..... evaluate (…….. used to occupy excel formulas in vba) with row,
Correct! Good! - Because you understand this, it makes the explanation for me much easier.
We are really only interested in understanding what is , =ROW(1:" & rng.Rows.Count & ")" , in excel spreadsheet formula
Question: _(ii)b) What is =ROW(1:" & rng.Rows.Count & ") ? ( in excel spreadsheet )
For your range, rng = B4:D8 , rng.Rows.Count = 5
=ROW(1: " & rng.Rows.Count & " )
=ROW(1: " & 5 & " )
=ROW(1:5)
( Green is Excel Spreadsheet, Blue is VBA in string “ “ in Evaluate( “ “ ) )
The excel spreadsheet Row( ) function is usually like for
Row( A1) = 1
but can also return a “vertical” array of values like
Row( A1:A2) = 1
Row( A1:A2) = 2
Inside VBA Evaluate “ _ “ we find that we are decoupled from spreadsheet absolute values, and so in inside VBA Evaluate “ _ “
Row(A1:A2) = Row( 1:2) = 1
Row(A1:A2) = Row( 1:2) = 2
ROW(1:5)
It is like
1
2
3
4
5
( Sometimes we may write
{1
2
3
4
5}
or sometimes we may write in excel spreadsheet convention , ={1;2;3;4;5} , but usually this means the same thing)
Note: For “vertical” array some excel use ; but some Excel use \ – So sometime you may need ={1\2\3\4\5}
Sometimes if we are lucky, Excel will try to do array calculations and return you an array.
Like
Code:
{ 1 2 X { 6 8 = { 1, 6 2, 8
3 4 } 7 9 } 3, 7 4, 9 }
But if you ask it to do = Index ___ , ROW(1:5)_ ,_ Array{1 , 3} it tries to look at columns and rows not specified.
This should not work
Code:
= { 1 ? { 1 3
2 ? ? ?
3 ? X ? ?
4 ? ? ?
5 ? } ? ? }
???? So we have a problem ?,
But we can be lucky again, because then Excel will guess to see the following instead, ( actually its more complicated then that, more precisely it is due to Excel VBA Interception and Implicit Intersection , but we often say that Excel guesses things, as its often written to get things correct when you miss things out. ( In this particular case it is not clear if the phenomenum occurs by accident or design ) )
Code:
= { 1 1 { 1 3
2 2 1 3
3 3 X 1 3
4 4 1 3
5 5 } 1 3 }
( What is actually happening there above in those last two sketches is: -
If Excel is given a single row or a single column, but is being required to look at values of further adjoining rows and columns where no values are given, then the effect of the phenomena of Excel VBA Interception and Implicit Intersection is that in certain situation the missing values will effectively be taken as a duplication of the values in the given row or column )
So, Index will try to give us
Code:
_______ Index( ( , 1, 1 1, 3
2, 1 2, 3
3, 1 3, 3
4, 1 4, 3
5, 1 5, 3 )
So if your range is, rng = B4:D8
-__=
Code:
_B4 _C4 _D4
_B5 _C5 _D5
_B6 _C6 _D6
_B7 _C7 _D7
_B8 _C8 _D8
then Application.Index(rng, Evaluate("=ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("=ROW(1:5)"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1, 3}"))
-__ =
Code:
rng , 1,1 1,3
2,1 2,3
3,1 3,3
4,1 4,3
5,1 5,3
-__=
Code:
_B4 _C4 _D4 1,1 1,3
_B5 _C5 _D5 2,1 2,3
_B6 _C6 _D6 3,1 3,3
_B7 _C7 _D7 4,1 4,3
_B8 _C8 _D8 5,1 5,3
Code:
= _B4 _D4
_B5 _D5
_B6 _D6
_B7 _D7
_B8 _D8
Originally Posted by
Amelynn
........use an array to determine the rows as well......
Sure, this is no problem:
One way, for example, for just 1st 3rd and 5th row
Change
Evaluate("={1;2;3;4;5}")
to
Evaluate("={1;3;5}")
-__ =Application.Index(rng, Evaluate("={1;3;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;3;5}"), Evaluate("={1, 3}"))
Code:
_B4 _C4 _D4 1,1 1,3
_B5 _C5 _D5 3,1 3,3
_B6 _C6 _D6 5,1 5,3
_B7 _C7 _D7
_B8 _C8 _D8
Code:
= _B4 _D4
_B6 _D6
_B8 _D8
Here a demo macro for you
Put some arbitrary values in your range "B4:D8" , then run this macro:
Code:
Sub Test()
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Range("B4:D8")
Dim RwsCnt As Long
Let RwsCnt = Rng.Rows.Count ' is = 5
Dim arr_List() As Variant
Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & Rng.Rows.Count & ")"), Array(1, 3))
Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & RwsCnt & ")"), Array(1, 3))
Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:5)"), Array(1, 3))
Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1,3}"))
Let Worksheets("Sheet1").Range("A40").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()
' To only select 1st 3rd and 5th row
Let arr_List() = Application.Index(Rng, Evaluate("={1;3;5}"), Evaluate("={1,3}"))
Let Worksheets("Sheet1").Range("A47").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()
End Sub
Alan
Reply With Quote
Bookmarks