Hello to all,
In a VBA procedure, how can I store in an array or variable the result of the array formula shown below?
Thanks in advance for any help.Please Login or Register to view this content.
Hello to all,
In a VBA procedure, how can I store in an array or variable the result of the array formula shown below?
Thanks in advance for any help.Please Login or Register to view this content.
Hello,
Somebody knows how to do it?
Thanks
Is this what you are asking?
Please Login or Register to view this content.
Hello jindon,
The array formula actually is
and it returns the column numbers in range A1:E1 ofPlease Login or Register to view this content.
values in range J1:M1 and if is not present, returns "0". So, this worksheet formula returns something like
But if I assign in this wayPlease 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?Please Login or Register to view this content.
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
Thanks in advancePlease Login or Register to view this content.
How are you outputting?
Note your sample is dealing with horizontal range, if vertical range, you need to transpose other way around.Please Login or Register to view this content.
Hello jindon,
Yes, thanks it works.
One more question.
Now works fine like this (with sheet names and ranges written explicitely/fixed)
But how can I insert variables to this expression to make it dynamic?, I mean. Replace by variables the sheet names (in red)Please Login or Register to view this content.
and replace with variables the ranges (in blue)
something like.
Because for example I would like to put different sheets names there and for the ranges I have another variable for last columnPlease Login or Register to view this content.
used and both parameters are stored in variables.
I hope make sense,
Thanks for the help.
Short cut notation of Evaluate method [ ] doesn't accept variables.
Please Login or Register to view this content.
Hello jindon,
Thank you!
I'm testing like below, but I receive run-time error 1004, error defined by application or object.
I'm not sure where is the error here. Var2 contains the name of the sheet.Please Login or Register to view this content.
Thanks for the help
1) Syntax of Evaluate method must be string, so ActiveSheet.Range(Cells(1, 2), Cells(1, 5)) must be its address.
2) Also Evaluate method takes address from ActiveSheet, so you need to change the 2nd range likePlease Login or Register to view this content.
Please Login or Register to view this content.
Thanks so much.
It works so fine with your suggestion.
Best regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks