Array to named range conversion...

The intention of my macro is to put all the wb's sheet names into 
named range as per the code below. Then, use the named range as th
list in a "data validation" cell, which will use the named range as th
list.

I've successfully been able to convert the array into a range, althoug
i can only populate the range horizontally (columns x 1 row). if i tr
to populate a vertical range, i get all the same value (the value o
index 1). I get similar results with the named range. And when I try t
use it in data validation, I get an error.

My workaround is to use the code to put the values into a horizonta
range, then use a named range to reference that range. Seems clunky an
unnecessary to include the intermediate step. Why not go from an arra
to a "n x 1" named range (vs a "1 x n")?  



Code
-------------------
    Sub updatesheets()
  
  Dim m()
  ReDim m(ActiveWorkbook.Sheets.Count)
  
  For Each sht In ThisWorkbook.Sheets
  m(n) = sht.Name
  n = n + 1
  Next sht
  
  ActiveSheet.Range("a1").Resize(1, (n - 1)).Value = m
  
  End Su
-------------------

--
i-Zap
-----------------------------------------------------------------------
i-Zapp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=576
View this thread: http://www.excelforum.com/showthread.php?threadid=47912

0
10/25/2005 2:31:41 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
578 Views

Similar Articles

[PageSpeed] 4

Just make it a 'vertical' array to start with:

Sub updatesheets()
    Dim Sht As Worksheet
    Dim N As Integer
    ReDim m(1 To ActiveWorkbook.Worksheets.Count, 1 To 1)

    For Each Sht In ThisWorkbook.Worksheets
        N = N + 1
        m(N, 1) = Sht.Name
    Next Sht
    Range("a1").Resize(N).Value = m
End Sub


-- 
Jim
"i-Zapp" <i-Zapp.1xgqyc_1130252709.5117@excelforum-nospam.com> wrote in 
message news:i-Zapp.1xgqyc_1130252709.5117@excelforum-nospam.com...
|
| The intention of my macro is to put all the wb's sheet names into a
| named range as per the code below. Then, use the named range as the
| list in a "data validation" cell, which will use the named range as the
| list.
|
| I've successfully been able to convert the array into a range, although
| i can only populate the range horizontally (columns x 1 row). if i try
| to populate a vertical range, i get all the same value (the value of
| index 1). I get similar results with the named range. And when I try to
| use it in data validation, I get an error.
|
| My workaround is to use the code to put the values into a horizontal
| range, then use a named range to reference that range. Seems clunky and
| unnecessary to include the intermediate step. Why not go from an array
| to a "n x 1" named range (vs a "1 x n")?
|
|
|
| Code:
| --------------------
|    Sub updatesheets()
|
|  Dim m()
|  ReDim m(ActiveWorkbook.Sheets.Count)
|
|  For Each sht In ThisWorkbook.Sheets
|  m(n) = sht.Name
|  n = n + 1
|  Next sht
|
|  ActiveSheet.Range("a1").Resize(1, (n - 1)).Value = m
|
|  End Sub
| --------------------
|
|
| -- 
| i-Zapp
| ------------------------------------------------------------------------
| i-Zapp's Profile: 
http://www.excelforum.com/member.php?action=getinfo&userid=5768
| View this thread: http://www.excelforum.com/showthread.php?threadid=479125
| 


0
jrrech (1932)
10/25/2005 3:52:16 PM
that's a step closer, but I still don't have the 'named range
configured properly yet. 

at the end of your code, I added the following to create the name
range:


Code
-------------------
      ActiveWorkbook.Names.Add Name:="mynamedrange", RefersTo:=myarra
-------------------
where 'myarray' is the array created by the macro.

if i then look at the newly created named range via Insert|Name|Defin
in Excel, then I get as a formula what looks like the classical arra
syntax, complete with brackets { }...


Code
-------------------
      ={"Sheet1";"Sheet2";"Sheet3"
-------------------
Problem is that it won't work as an argument for the data validatio
list source. How do I un-array it?



--
i-Zap
-----------------------------------------------------------------------
i-Zapp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=576
View this thread: http://www.excelforum.com/showthread.php?threadid=47912

0
10/25/2005 4:48:35 PM
>at the end of your code, I added the following to create the named
range: ActiveWorkbook.Names.Add Name:="mynamedrange", RefersTo:=myarray


That really can't be true since I don't have a "myarray" in my code.

Once the sheet names are listed create a name for that range, not for the 
array.  At least if you're sticking with what you said in your first 
message - "Then, use the named range as the
list in a "data validation"..."

-- 
Jim
"i-Zapp" <i-Zapp.1xgwid_1130259993.6348@excelforum-nospam.com> wrote in 
message news:i-Zapp.1xgwid_1130259993.6348@excelforum-nospam.com...
|
| that's a step closer, but I still don't have the 'named range'
| configured properly yet.
|
| at the end of your code, I added the following to create the named
| range:
|
|
| Code:
| --------------------
|      ActiveWorkbook.Names.Add Name:="mynamedrange", RefersTo:=myarray
| --------------------
| where 'myarray' is the array created by the macro.
|
| if i then look at the newly created named range via Insert|Name|Define
| in Excel, then I get as a formula what looks like the classical array
| syntax, complete with brackets { }...
|
|
| Code:
| --------------------
|      ={"Sheet1";"Sheet2";"Sheet3"}
| --------------------
| Problem is that it won't work as an argument for the data validation
| list source. How do I un-array it?
|
| .
|
|
| -- 
| i-Zapp
| ------------------------------------------------------------------------
| i-Zapp's Profile: 
http://www.excelforum.com/member.php?action=getinfo&userid=5768
| View this thread: http://www.excelforum.com/showthread.php?threadid=479125
| 


0
jrrech (1932)
10/25/2005 6:44:38 PM
I changed the array name for clarity's sake. Apparently, it had the
opposite effect.

I have not strayed from my original request, which is to seek a method
that takes the VB generated array and pumps it directly into a
named-range, but that is compatible with the source field of list-type
data validation, without needing to create an intermediate worksheet
range.

The issue, I believe, is that data validation's source field is looking
for a -cell reference-. And by putting a named-range into that field
that's actually just a list of string items, it returns an error.

Seems like the long way around the barn, but I was hoping that someone
was hip to some Excel trickery that would streamline the solution.


-- 
i-Zapp
------------------------------------------------------------------------
i-Zapp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5768
View this thread: http://www.excelforum.com/showthread.php?threadid=479125

0
10/25/2005 8:09:29 PM
Reply:

Similar Artilces:

Lookup from a range on separate sheets
Hi, I am trying to look up values from the same fixed range (c50:E200) in 45 sheets and extract the values onto one sheet (same workbook) 3 columns at a time (ie first sheet B-D, second sheet E-G etc). I can't work out how to put the sheet name (or a reference to the sheet number) in a formula in the destination sheet - is it possible or do I have to use a macro? I can write the code for a macro, but as it is statistical process control limits which are added to each month, it means rerunning the macro every time Any help much appreciated! Paul Paul, In the sheet/cells that ...

Count File Names
Hi All, I having an issue with version control on Buisness cases. I currently have a spreadsheet that looks into a folder and picks up details from a number of files. However, they require version control numbers so there will eventually be multiple files say 2010-01 v1.0, 2010-01 v1.1. Is there a way I can: 1) Get a macro to count the number of 2010-01 files there are in the folder, and 2) Get my version control sheet to look at the latest version of the file (in this example: 2010-01 v1.1) Cheers, Chris See if this code works. I assume that 2010-01 is year and month. ...

M2004: Change name on home page?
I'm using Money 2004. On the home page it says "So-and-so's Money Home Page". How can I change the name that appears there? -Rod > I'm using Money 2004. On the home page it says "So-and-so's Money Home > Page". How can I change the name that appears there? Figures... I always seem to find a solution right after posting a question! Click Accounts & Bills, Account Setup, Update Personal Information. -Rod ...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

Name prefixing in reply message
All, I'm not even sure what terms to google for related to this problem. If you get an email from someone in the Office XP version of Outlook, and you click reply to that email, then start typing inside the portion of the message that contains the original message you replied to, it will automatically prefix everything you write with your name. How does one turn this off? Thanks in advance! TwistedPair wrote: > I'm not even sure what terms to google for related to this problem. If you > get an email from someone in the Office XP version of Outlook, > and you click...

create chart from non adjacent range
I need to know how to do this Select one area, then hold CTRL while selecting the next area, etc. Then run the Chart Wizard as usual. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "rita" <rita@discussions.microsoft.com> wrote in message news:18C9672C-0EAB-401C-8798-4C51158EAF8B@microsoft.com... >I need to know how to do this ...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Comparing first and last names in two lists #2
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

Different display names
Hi Is there any way to have an account display differently in the FROM field compared to the GAL (Exchange 2003). We would like the user to appear in the GAL as "Bob User" but when an external recipient reads an email it should be displayed as "Bob of Company <bob.user@company.com>". Cheers Stuart ...

newbie question on multi-dimensional array
Hi all, I have a real lame question about multi-dimensional array operation. Suppose I have defined a two-dimensional array "Array(10,10)". If I want to use the Excel function "Average" to compute the average value of Column, say 10, of that 2D array, is there any way to realize it? I tried "Application.Average(Array(:,10))", but apparently the Excel doesn't like that. (You can see I am a Matlab guy, so please don't laugh at me:) ) Any comments are extremely welcome. -- sammus ------------------------------------------------------------------------...

File name from a cell
I am trying to have date in a cell become the file name and appear i the save as dialog box as the file is saved the first time -- Message posted from http://www.ExcelForum.com One way: Option Explicit Sub testme01() Dim mySuggestedFileName As String Dim myFileName As Variant With Worksheets("sheet1") mySuggestedFileName = "c:\my documents\excel\test\" _ & Format(.Range("a1").Value, "yyyy_mm_dd") & ".xls" End With myFileName = Application.GetSaveAsFilename _ ...

Unlocking named ranges
I am working with a spreadsheet created by a former employee. He used named ranges a lot. I am trying to understand the worksheet. When I see an equation in a cell like =Sum(Shipments) I feel I know what it means. But how do I learn what the actual cell reference is ie = Sum(D2:G2).? TIA Insert a new, empty worksheet into the workbook. Then you can use The Insert-> Name-> Paste menu option and click on the Paste List button to get a listing of all the names and the definitions "JohnL" wrote: > I am working with a spreadsheet created by a former employee. He used...

Adding company name to the "From" field
Hopefully this is an easy problem. We are running Exchange 2003 on Windows Server 2003 in a mixed domain. (Win2k and Win2003 servers) Lets say I am working for a company named "XYZ". The owner of the company wants the "from" line in the email client to say "Bill Smith, XYZ Company". Unfortunately I can not find a way to do this. Any help is appreciated. Bob Hi Bob, Just use ADModify to rewrite the Display name for every user in your company, or for users of that company (if companys are sharing and AD) ADModify can be found here: http://www.gotdotnet.co...

edit suggest names when completing To, cc and bcc fields
Apparently I don't have an Address Book linked to Outlook 2000. No matter, because I seem to be ok with the suggest names feature, which I guess is not Address Book-linked, but linked to a memory bank of names and corresponding addresses typed in during the past. However, here is my question: How do you edit this Suggest Names list? In particular, A. Suppose you want to stop Outlook from suggesting a particular name because it is not current anymore. Worse, you might inadvertantly click on it and send msg to somebody you don't want to receive it. Is there a way to delet...

array formula Excel 97 (Poker)
Cells A1:A7 each contain a random integer from 1 to 13, with possibl repetitions. I have been trying to create a single formula that will test, for cel A1, for the presence of each of the next lowest four numbers in th range A!:A7 So if A1 contains 9, I would like the formula to return 1 if and onl if 8,7,6,and 5 all occur in the range somewhere, and return otherwise. This is tantamount to testing whether seven cards contain a five car straight headed by the card in A1. I just can't get the right combination of array or sumproduct to d it. I just know ther must be one........... Bil...

reading file name from folder function
I am using Access 97. Just wonder if there is a fuction which can read all the file name from a folder and put it to a column in a table. Please let me know. Thanks. Hi. >I am using Access 97. Just wonder if there is a fuction which can read all >the file name from a folder and put it to a column in a table. Please see the getFileInfo( ) function in the tip, "How to compare the contents of two directories," on the following Web page for an example: http://www.access.qbuilt.com/html/vba1.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http...

Domain name not always needed to log into OWA?
This is something I've wondered about for a while. Why is it that only some of our new Exchange Server installs insist on the OWA login <up to 13 chars. of domain name>\<user name> while others have no trouble with only <user name>? "Julie" <julie@somewhere.org> wrote: >This is something I've wondered about for a while. > >Why is it that only some of our new Exchange Server installs insist on >the OWA login <up to 13 chars. of domain name>\<user name> while >others have no trouble with only <user name>? Probably b...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

Report names in listbox
I recently upgraded to Access 2007 and am having some trouble with the changes. I have a form with a listbox in which I would like to display the names of all the reports in the database. I've tried using the following as the rowsource for the listbox, but in form view nothing is displayed. Does anyone know how to do this? TIA, Ken Warthen kenwarthen@gmail.com "SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],1))<"~") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;" I think you made a slight typo. Try (((Left([Name],1))<>&quo...

Send Emails With A Different Name Than How Its Listed in the GAL
Hi all, I have a quick question: My client is unhappy with how his emails get sent out from MS Outlook. The GAL on our server lists names as [Last Name], [First Name]. This is how the majority of our clients want it. However, he wants his to be [First Name] [Last Name]. So, all recipients getting emails from him will see his name as First Name, Last Name. We can't do this for him on our GAL, since it would obviously cause an inconsistency if we did it only for one name. But, I figured I'd post here to see if anything else can be done to accomodate him. Thanks in advance for your help...

Switching Names Around
Hey gurus- I am getting a list of names imported into Excel. All the names are in the order of LAST NAME FIRST MI/[SPOUSE] FIRST [SPOUSE] MI. All of them have their last name at the beginning, and they're all merged into the same cell. I am trying to print out mailers, so I need the 1st name moved to the back of the line so it prints out normally. How would I write a macro to do this? The only way I can think of is to signal is off the very 1st SPACE- everything before that space gets added to the end of the line. Is this possible to do? How do I do this? I'm not very good at VB, so...

Access server name?
Hi Experts: I have been using SQL Server 2005 and I'm now trying to move to Access (Microsoft Office Access 2007). One question, with SQL Server 2005, there is a Windows Service called "SQL Server" which is running all the time and ready to be connected from a database application. In the case of Access, what is the equivalent program? I do not see any Access program in the Windows Service List. Thanks in advance! Polaris In news:OC1gc%23kpHHA.4872@TK2MSFTNGP03.phx.gbl, Polaris <etpolaris@hotmail.com> wrote: > Hi Experts: > > I have been using SQ...

Using a named range as a data source for a chart
Hi, I'm trying to build a chart that that has a named range as its data source. The named range refers to a table that is 2x3 and is called geographydata. Is there any way that I can have the source data for the graph to be geographydata? Thanks! Michael You can enter the name, preceded by the worksheet name and exclamation point, for the data range, but Excel will convert this name to its cell address. If you want the chart to be dynamic as the name changes its size, you have to define a name for each series' data. This will not dynamically change the number of series in the ch...