Store array formula content in a VBA array

[cgkmal]

Hello to all,

In a VBA procedure, how can I store in an array or variable the result of the array formula shown below?

Please Login or Register  to view this content.

Thanks in advance for any help.

[cgkmal]

Hello,

Somebody knows how to do it?

Thanks

[jindon]

Is this what you are asking?

Please Login or Register  to view this content.

[cgkmal]

Hello jindon,

The array formula actually is

Please Login or Register  to view this content.

and it returns the column numbers in range A1:E1 of
values in range J1:M1 and if is not present, returns "0". So, this worksheet formula returns something like

Please Login or Register  to view this content.

But if I assign in this way

Please Login or Register  to view this content.

when I print the value of x only prints the first element. How can I get value i within x variable?

For example the values in range A1:G1 ar below:
# A B C D E F G
1 A B D F R H J

Values in J1:M1 are:
# J K L M
1 D R J K

So, the formula returns

Please Login or Register  to view this content.

Thanks in advance

[jindon]

How are you outputting?

Please Login or Register  to view this content.

Note your sample is dealing with horizontal range, if vertical range, you need to transpose other way around.

[cgkmal]

Hello jindon,

Yes, thanks it works.

One more question.

Now works fine like this (with sheet names and ranges written explicitely/fixed)

Please Login or Register  to view this content.

But how can I insert variables to this expression to make it dynamic?, I mean. Replace by variables the sheet names (in red)
and replace with variables the ranges (in blue)

something like.

Please Login or Register  to view this content.

Because for example I would like to put different sheets names there and for the ranges I have another variable for last column
used and both parameters are stored in variables.

I hope make sense,

Thanks for the help.

[jindon]

Short cut notation of Evaluate method [ ] doesn't accept variables.

Please Login or Register  to view this content.

[cgkmal]

Hello jindon,

Thank you!

I'm testing like below, but I receive run-time error 1004, error defined by application or object.

Please Login or Register  to view this content.

I'm not sure where is the error here. Var2 contains the name of the sheet.

Thanks for the help

[jindon]

1) Syntax of Evaluate method must be string, so ActiveSheet.Range(Cells(1, 2), Cells(1, 5)) must be its address.

Please Login or Register  to view this content.

2) Also Evaluate method takes address from ActiveSheet, so you need to change the 2nd range like

Please Login or Register  to view this content.

[cgkmal]

Thanks so much.

It works so fine with your suggestion.

Best regards