+ Reply to Thread
Results 1 to 5 of 5

Changing array names creates error

  1. #1
    Registered User
    Join Date
    08-07-2004
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    62

    Changing array names creates error

    I was looking for some parts to build a form and found one that's almost what I need - thanks mehmetcik. What I'm having an issue with is that the array can be renamed and still work just fine, but changing the list items causes an error. In the examples the first two run just fine (as long as all references to the array are changed also) but the third example causes an error and I am at a loss as to why. The problem workbook is attached.



    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Changing array names creates error

    What's the nature of the error?

    There are no named ranges for "Somebody" and "SomeEquipment" in the example workbook. That would throw an error.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    08-07-2004
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    62

    Re: Changing array names creates error

    Error is: Run-time error '1004' Method 'Range' of object '_Global' failed.

    I think you misunderstand my issue here. The original array, code sample 1, runs just fine. If all references to MyNames is changed to "MyOtherNames, the code runs just fine. Changing either of the first two arrays to include the changes in the third, or any changes for that matter, cause the error. My question is why the entire array can be changed out and work just fine as long as the included items are the same, but changing an item causes a problem. It appears to me the only place the array gets used is if one of the drop down boxes isn't completed and a box opens and asks for an input based on a reference to the items in the array. Seems like it ought to be a simple matter to change the headers on the dropdown boxes and change the MyNames array to match them. However, changing any of the items in the array causes the error. Hopefully the attached file with the "Employee" item changed to "Emp" shows the issue
    Attached Files Attached Files

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Changing array names creates error

    In your second workbook, the line
    Please Login or Register  to view this content.
    works perfectly and creates myNames as a 0 based array.

    The error is caused because there is no named range Emp
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Changing array names creates error

    I believe a do understand. The array items reference Named Ranges. You changed the array to include "Emp' but there is no Range("Emp") defined yet, and that throws the "Method 'Range' of object '_Global' failed." error.

    Range(MyNames(Count - 1))
    Evaluates to
    Range("Emp")

    Solution: create named ranges that are the same names as your Array items.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help on macro that creates two Workbooks with custom file names
    By joshuarobbins in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2015, 01:27 PM
  2. Changing Sheet Names based on cell value - Object doesnt support this property error
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-26-2013, 09:30 AM
  3. Modify VBA code that creates a list of Sheet Names
    By Coleen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-30-2013, 01:41 PM
  4. [SOLVED] A Macro that creates hyperlinks by matching the names of a column
    By kc07 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2012, 04:17 AM
  5. Workbook A creates B & uses worksheet names from A in B
    By dkub in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2011, 12:04 PM
  6. Array Formula creates error if too may cell chosen
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2006, 07:25 PM
  7. Replies: 4
    Last Post: 05-04-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1