LinkBack URL
About LinkBacks
Perhaps stupid question.
Is it possible to create a named range by referring to an Array as opposed to referring to a Range that contains the data? If so would appreciate code showing how to do it.
Perhaps stupid question.
Is it possible to create a named range by referring to an Array as opposed to referring to a Range that contains the data? If so would appreciate code showing how to do it.
Is this the sort of thing you mean?
Then with cell formula you could use the following to return Green
Please Login or Register to view this content.
=INDEX(RGB_ARRAY,2)
Andy, your example sort-of. It's a bit more complicated and I can't get my brain to arrive at a solution for indexing the array to get the proper data.
The code below works great. But it requires taking data from an array, putting it into a range, and then extracting columns of data from that range to create individual Ranges of Named Range data for use in Excel formulas. For efficiency, I'd like to change the "RefersTo" instruction to extract data directly from the Array "aDataArray".
Below is a small sample of the data range "rLogDataOnScratchSheet". The code loops through each column of the range and creates a Named range that points to the respective column of data. For example, Column A would become the first named range, Col B the second, etc.
Thanks in advance.Please Login or Register to view this content.
Last edited by toliphint; 08-09-2020 at 05:17 PM. Reason: Provide attachment
Your attachment is invalid... please use the instructions in the yellow banner near the top of this forum's window to upload a copy of your workbook.
Updated above. Tks.Originally Posted by Rick Rothstein
Given an array, this will put its elements in a name
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
An elegant solution. Thanks. When time permits, will evaluate against current code on my 200K+ element array and advise. Thanks again.
Many thanks mikerickson for the reply and again elegant solution, but it does not work for me. I have over 200K+ array elements and the length of a string is greatly exceeded even when parsing my array to smaller sub-sets.
Here's what I've concluded unless someone can prove otherwise:
1.
When a Range Name is added, it must point to (ReferTo) a Range and nothing else (except perhaps a string but too short for my app). When it points to an array of data, the created range names fail in Excel due to invalid data as indicated by information in the Name Manager (Ctrl-F3). It shows:
Value = {...}
RefersTo = {The data in the array}
2.
But when a range is used as the data source in the RefersTo instruction, we see the following in the Name Manager and all is good:
Value = {The data in the range}
RefersTo = The range which contains the data referred to/pointed to
So bottom line, despite being allowed by vba code, it seems for large data sets a range must contain the data to which a RefersTo instruction refers.
If this can be proven wrong, I'd love it because referring to an array of data as opposed to writing a range from an array and then ReferTo that range cannot be as efficient as referring directly to the array -- especially when dealing with very large data sets.
Comments/Thoughts?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks