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
450 Views

Similar Articles

[PageSpeed] 8

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 (1933)
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 (1933)
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:

Changing the Display Name causes users to see the old and the new Names as two mailboxes
After changing the Display name, for example, from "Jane Jackson" to "Jane Brown" users who kept Jane's old messages in their inbox, are seeing both Jane Jackson and Jane Brown as different users. sorting mail by the from field will of course cause a split between messages from the past and from the present. My question is: how can I join the old and the new names at the clients, so both Janes would as one entity? Thanks On 19 Jan 2007 05:13:14 -0800, avic2001@hotmail.com wrote: >After changing the Display name, for example, from "Jane Jackson" to >&q...

retail pro (DOS) file conversion
Has anyone converted inventory, vendor, and customer files from Retail Pro DOS to RMS? How is it done? Mark, when you say Retail Pro "DOS", do you mean Retail Pro version 7? Also, are you migrating from Retail Pro to RMS? I'd be interested in the reasons to migrate to RMS instead of Retail Pro version 8 which is windows-based. Thks. "Mark Turner American Retail Supply" wrote: > Has anyone converted inventory, vendor, and customer files from Retail Pro > DOS to RMS? > How is it done? > RMS is also MS SQL based. Retail Pro is not - "convol...

"Array Index Out of Bounds"
I recently purchased "Office Professional Ed 2003." With it came "Business Contact Manager." When I double click to open a particular contact or business contact, I get the above "subject" message. Is there a reason, fix, or solution for this? Thanks, John ...

additem to combobox with an array
hi, i'm trying to add itemns to an multicolumn combobox (3 columns) using an array. if i just try to add 1 column there is no problems, but when i try t add more columns to the combobox it wont work anymore. tips anyone? tia jock -- jock ----------------------------------------------------------------------- jocke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=76 View this thread: http://www.excelforum.com/showthread.php?threadid=47167 Did you change the .columncount property? I made a small userform and picked up some values off a worksheet to create...

Works conversions
Ok, well, I've had alot of experience with works. I used to have works 3.x, and it's format for spreadsheets was .xlr or .xls. Ok, that was fine. Then, I had works 6.0, and it's format for spreadsheets was .wps. I changed to XP Pro, and had to put on Office XP, so now I can't open the wps files, and I don't have Works 6.0 anymore. Is there any way I can convert a wps file to something that Excel will read, and still have the data? You need to (a) open the files in Works and save in Excel format, or (b) buy a conversion program like Conversions Plus from www.datav...

VBA, Changing Datafield Name
Hi ALL This is my first post, so excuse me for any faux pas on etiquette. I'm creating various pivot tables by VBA code. For each datafield I change the name so that the columns would not be too wide, and so that I don't get the "Sum of " thing. My methodolgy of changing the name relies on the datafield starting with 'Somme' (Note that I use French excel). I notice that Excel 02 has 'Somme de ' meaning that my code comes to a halt. I have stuck in a few lines of 'IF' statements relating to Excel version to remedy this. However, this is not all encom...

Array+Array
Hi, I have 2 (or more) arrays, how can i combine them into 1? example: Array 1 = "1, 2, 3, 4" Array 2 = "A, B, C, D, E, F" Result: Array_Combined = "1, 2, 3, 4, A, B, C, D, E, F" br, Danny On Jan 7, 9:23=A0am, Danny <dannypct...@gmail.com> wrote: > Hi, > > I have 2 (or more) arrays, how can i combine them into 1? > > example: > Array 1 =3D "1, 2, 3, 4" > Array 2 =3D "A, B, C, D, E, F" > > Result: > Array_Combined =3D "1, 2, 3, 4, A, B, C, D, E, F" > > br, >...

Japanese in file names
I have little or no problem using Japanese within a word or entourage document (apart from sometimes yampy character display within a word document that can be irritating), but none of the office apps can handle Japanese in the file names. Totally screws it up. Does anyone else suffer from the same problem, and if so can it be fixed, without buying a Japanese version of office. If I do get a Japanese version of office, is it possible to get the menus up in English? ...

array
Dear programmer I don't understand the array can any one give very easy example to understand the types of array ( I can use command button and text box) Example 'Don't forget to write notes for me ' the next line will define the array. Dim a(2) As String ' the next lines define the data of array a(0) = "1" a(1) = "2" a(2) = "3" And so on Notes: The array is very difficult please give me easy example "a" <A@a.com> wrote in message news:uy9smdVUIHA.4476@TK2MSFTNGP06.phx.gbl... > Dear programmer I don't understa...

how to retrieve current values assigned for names used in EXCEL?
I got a spread sheet from some one else. There were formulas used using names. I like to retrieve the values used for different names used in the formulas. Please let me know for any questions. Rao goto <insert><names><define> and you will be able to find the names and either cells, values or formulas assigned to the names. "yrk" wrote: > I got a spread sheet from some one else. There were formulas used using names. > I like to retrieve the values used for different names used in the formulas. > > Please let me know for any questions. > > R...

How do insert name automatically atop of each pg of doc?
Hello, How do I automatically insert name on each page of document like inserting page numbers? My professor told me not to type it in but to insert and I have not been able to do so. This is a new program for me, Word 2007. I have to submit my paper in MLA form and do not wish to lose points because for this. Thanks for your help. Click on the Insert tab of the Ribbon and then in the Header and Footer section, click on Header and make your choice from the options presented or click on the Edit Header button to get a blank header pane in which to insert the name in the...

Retrospective Cell Naming
If I set up a spreadsheet that includes references of the form =C3 etc, if I then define a name for cell C3 (eg "cost"), then I have to manually go back through the spreadsheet to find references to C3 and change them to the name. Is there a clever way (other than find and replace) to automatically or via macro etc to go through a spreadsheet at replace all relative references with the name reference? Any ideas? See "Apply Names" in Excel help. -- Regards Dave Hawley www.ozgrid.com "pom15595" <pom15595@discussions.microsoft.com> wro...

Dynamic Range #3
I have the following function defining the range "Grips". (The first value to be used in the "M" column is in Row 3.) =OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M),1) I then use the name "Grips" as a validation list "Source". It works fine other than I get all the values plus a blank space at the bottom of my drop-down list. Any ideas? Sandy What's in M1:M2? Remember your =counta($m:$m) is including those cells, too. I'd put something in both cells. If I want to make it look like that cell is blank, I'll use a formula: ="" ...

Define Local Name using Name Box
I have an Excel 97 file example with sheets "Red" and "Blue". In cell Red:A1 the range name "SheetName" has been defined. I would like to define the same range name "SheetName" in cell Blue:B1 as a local name, using the Name Box on the left-hand side of the formula bar, rather than Insert | Name | Define from the menu. I have seen the syntax previously on this, and have forgotten the procedure. Thanks for your help. Chris S. Just include the sheet name in the name box: Sheet2!SheetName note that SheetName will still be a workbook-level na...

Print range question
I've made a spreadsheet with several tabs. In this sheet i've selected a certain print range so only the information i need gets printed. But when i try to print the sheet, or on the print preview everything on the sheet gets printed.. I thought that only things inside the print range (visible on the sheet with a thin line), would be printed... Is there a way that i can define wich range only can be printed besides the print range option? I have made this sheet with Excel2007 and converted it to Excel2003. Is there something gone wrong with this conversion? I cannot reproduce this....

how do I create an array
I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3:G11}),""))) please help ...

Force exchange to use my default display name
Hi guys, Is there a way to force Exchange to chande the display name ??? Here's a exemple: If our web site send a email to someone by relaying on the exchange server. With the following information Sender name: "Sales" Sender email: sales@paradoxusa.com To: Customer email adress CC: Allan Tox When I receive the copy of the email I want to see my display name of sales@paradoxusa.com wich is Customer Service Paradox Security and not from Sales... Can you guys have a idea where I have to do my change on the Exchange server ? Thank you...

how to save a query with new Name VBA?
I have a very complicated form to create a query with all kinds of dropbox, listbox, combo etc. I'd like my users to save each query with his own queryname in VBA. It is like a MSG box that asks for a name of the query, then save the current query as the new query. So, next time, users do not need to choose all the options again. Is there any function or Macro in MS Access to do so? Thanks, WoodyBarry You can either use a text box or an input box to get the name. The use the CreateQueryDef method to create and save the query. -- Dave Hargis - MVP Access <woodybarry@gmail.com> w...

Referencing a sheet by taking its name from a cell
I'm not even sure the title of this post is correct but it's as clear as I could make it! I'm creating a summary sheet of data from many other worksheets. The worksheet names are mmm-yy (e.g. Nov-07). The top row of the summary sheet will contain those same names, with the summary data in the column below. I'm trying to find a way to reference the correct sheet by utilising the name in the top row. Something like ='A2'!C7 , I suppose, but this particular attempt didn't work. Can someone help? Thanks! Paul Assuming that you have "Nov-07"(sheet name) in...

Hurry Up Now, Free Check Of $327 With Your Name.
Hurry Up, Free Check Of $327 With Your Name. Quickly open below website and click any one of the Red Color Text in the website and enter your payeename and address where to get your check. The secret website link is http://googleadsense-highpaying-keywords.blogspot.com/ ...

Array saving question (lbound)
Hello! I am saving an array to a file and then load it again. Open sPath For Binary As #iFile1 If uLoad Then Get #iFile1, , uArray Else Put #iFile1, , uArray End If My array looks like this: Redim myArr(1 to 3) as long myArr(1) = 100 myArr(2) = 500 myArr(3) = 10000 When I load it again, I say: Redim MyNewArr(1 to 3) and call the above sub. For some reason, MyNewArr now looks like this: MyNewArr(0) = 100 MyNewArr(1) = 500 MyNewArr(2) = 3 When I try to access MyNewArr(3) an out of bounds error is thrown. Can somebo...

Conversion of Access97 to Access 2000
Hi All, I need an urgent help!! I'm converting an Access97 DB to 2000. After I converted the DB through Tools, DB utilities, I'm receiving an error message:"Couldn't find installable ISAM". What should I do to resolve this problem? Thanks Hi Tamer, I take it you're getting this message when running the database after converting. See http://support.microsoft.com/default.aspx?scid=kb;en-us;209805&Product=acc2000 for the usual causes of this message and how to fix most of them. On Tue, 21 Oct 2003 14:09:46 -0700, "Tamer" <anonymous@discussions...

Accessing the CCD array of the camera using MFC
Hello, I am capturing the images from the camera using VC++ MFC . And each image is of size 1280*1024. When I want to do processing over certain area, actually I want to divide the Image height(1024) into 4 blocks (256,256,256,256) keeping the width (1280) constant. So that I will get the whole image into 4 small images. Can anyone please kindly tell me How can I able to do this ? I am thinking of accessing CCD array, If accessing CCD array is a solution for this.. Please help Thanks in advance James First, you need to have a device driver that connects to the camera. This is not an MFC ...

distinguished name of a group
Can somone give me the proper syntax for the distinguished name of a (security)group? Thanks, Steve "Stephen M" <smeier-spamless@primelink1.net> wrote: >Can somone give me the proper syntax for the distinguished name of a >(security)group? It's the same as the DN for any other object in the AD. You can use either LDP.exe or ADSIEDIT to see what the name might be on any specific object. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Names on 1099s
What field does the name that prints on 1099s come from? Here's my problem: We have a lot of vendors that are sole proprietors using dba names. A/P entry will only have the dba name when they receive the invoice, so that needs to be the name that comes up in CP lookups. The individual owner's name needs to appear on 1099s, and I don't see a separate field for this. Mike, I believe it is the Vendor Check Name Kind regards, Leslie "Mike" wrote: > What field does the name that prints on 1099s come from? > > Here's my problem: W...