allocate multi ranges to an array

[mohadin]

Hi all
I need to create an array form multi ranges, let say b2:f10 and range v2:v10

withe the formulas
I did come up with the attached sample but it not convenient as you can see
please any ideas
Thank you

 

[Marc L]

Hi !

A bit unclear what you need, anyway try this :​

    VF = Application.Index(Sheet1.[A1:V10].Formula, Evaluate("ROW(2:10)"), [{2,3,4,5,6,22}])

Do you like it ? So thanks to click on bottom right Like !
​

 

[Hui]

There is a technique shown here:
https://fastexcel.wordpress.com/201...-in-excel-formulas-versus-functions-shootout/
which maybe applicable

 

[mohadin]

Hi !

A bit unclear what you need, anyway try this :​

    VF = Application.Index(Sheet1.[A1:V10].Formula, Evaluate("ROW(2:10)"), [{2,3,4,5,6,22}])

Do you like it ? So thanks to click on bottom right Like !
​

Thank you MR. Marc L
It is what I wanted to do.
appreciate

 

[mohadin]

There is a technique shown here:
https://fastexcel.wordpress.com/201...-in-excel-formulas-versus-functions-shootout/
which maybe applicable

Hi MR. Hui
Thank you for reply
The Link do not like to open^^^
Any solution
Thank you

 

[mohadin]

Hi to all again,
The code line by MR. Marc L is perfect
Now what If I need to do the same but one of the column with formula to be allocated to the array as values and the rest of the columns with formulas?
Is it possible ?
Thank you

 

[Marc L]

Nope directly !

But if you first create a formula array then another array for value,
so you can replace formula in first array by value of last array …
​

 

[mohadin]

Nope directly !

But if you first create a formula array then another array for value,
so you can replace formula in first array by value of last array …
​

HI Mr. Marc;
First Thank you very much in Deed
but in this technique will absorb time If we have a huge array. Right?
I would prefer your code line and before that may convert the desired column to values, what do you think is faster for huge array?

 

[Marc L]

It depends on « huge » but as working in memory is fast …

Best is to try to forge your opinion !
​

 

[Debaser]

Hi to all again,
The code line by MR. Marc L is perfect
Now what If I need to do the same but one of the column with formula to be allocated to the array as values and the rest of the columns with formulas?
Is it possible ?
Thank you

It is, but it gets ugly. For example, to return the formulas from the first 4 columns and the values from the last (21st) column, you could use:

    Dim vF, vV, vM

    With Range("B2:V10")
        vF = .Formula
        vV = .Value

        vM = Application.Index(Application.Choose(Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2), vF, vV), [row(1:9)], Array(1, 2, 3, 4, 21))
    End With

 

[mohadin]

Hi Mr. Debase
let me say that...

WWWWhat?
let try to understand
Thank you In deed

 

[mohadin]

It is, but it gets ugly. For example, to return the formulas from the first 4 columns and the values from the last (21st) column, you could use:

    Dim vF, vV, vM

    With Range("B2:V10")
        vF = .Formula
        vV = .Value

        vM = Application.Index(Application.Choose(Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2), vF, vV), [row(1:9)], Array(1, 2, 3, 4, 21))
    End With

Hi Mr. Dbaser
I after first look
It is really very nice
It is as MR Marc L suggest as well
Thank you In deed
Appreciate

 

[Marc L]

You can simplify Debaser's smart way with only the final array :​

With [B2:V10]
    VA = Application.Index(Application.Choose([{1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}], _
        .Formula, .Value), [ROW(1:9)], [{1,2,3,4,5,21}])
End With

Array = Index(Source Data, Rows Source, Columns Source)

Source Data = Formula / Value from B2:V10 range.
The array [{ … }] within Choose function represents
for each column number if it's a formula (1) or a value (2)
(by the order of these properties at end of this function) …

Rows source is the array 1 to 9 (B2:V10 = 9 rows) …

Columns source is the array 1,2,3,4,5,21 (B to V = 21 columns)
the reason why within Choose there are 21 numbers, one by column …

So in this sample, the third column (D as the first is column B)
is by value. Last column (V) is referenced in the array by formula (1)
but as there are only values within this column …

Formula property returns value if there is no formula within the cells …
​

 

[mohadin]

Hi MR. Marc L
It is a very clever
One issue is that in the debugger It takes the value in column V as variant/string witch might cause an issue some where (just guess)

 

[Marc L]

Maybe not but you can mod 1 by 2 in the Choose area …
​

 

[mohadin]

Maybe not but you can mod 1 by 2 in the Choose area …
​

Exactly
Thank you
thank you all
some great forum