+ Reply to Thread
Results 1 to 8 of 8

If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

  1. #1
    Registered User
    Join Date
    08-08-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    12

    If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    I've tried various things but I can't figure it out. I redim the array so I don't know why I keep getting the subscript out of range on the array. I have tried it two different ways. The program runs but the array doesn't populate correctly.

    I have 4 columns of data, if the last column (D) has NA (from a previous formula) I want to write that row's values in Column A & B to the first line of an array. I will then use that array to provide information via a userform to the end user who will populate the information for the 3rd & 4th variable of the array -- all of the information will then be transposed to a master spreadsheet that is updated weekly.

    HTML Code: 
    Any help is greatly appreciated!!

    Thank you

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    Where do you get the error?

    Also, what is the value of NumRows?
    If posting code please use code tags, see here.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    At first glance, it appears that this line:
    Please Login or Register  to view this content.
    should be before this line:
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    Is myarray meant to be dynamic?

  5. #5
    Registered User
    Join Date
    08-08-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    I moved the
    HTML Code: 
    and it did the same thing.

    NumRows varies -- in my current week's spreadsheet it is 430

    Yes it is meant to be dynamic

    I pause the macro right after this because I am trying to make sure this part works before I continue. I do not get any pop up error messages when it runs, but when I put my mouse over the variables to make sure everything works, they all do except when I put my variable over "myarray" it gives me "<subscript out of range>" and if I add code to transpose the array (at the bottom) to see if it's working -- nothing happens. The code runs, it just doesn't do what it's supposed to be doing.....

    (MY first array though so I might be missing something simple...)

  6. #6
    Registered User
    Join Date
    08-08-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    OH!

    I forgot this part

    HTML Code: 
    That is before the ReDim Statement and sets a value of 57 in my current spreadsheet

    Then I reset the x to 1 for the loop -- here is the updated code... sorry:

    HTML Code: 
    When I adjust the x = 1 to within the loop instead of outside of it -- it stops saying subscript out of range and just says the array is empty.
    Last edited by roxybethany; 03-03-2014 at 04:59 PM.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  8. #8
    Registered User
    Join Date
    08-08-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: If Value is NA then Populate Array - Keep Getting "Subscript Out of Range"

    Example 03.03.14.xls

    Here you go!

+ 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. Breaking out Comma Delimited Fields - VBA Error - "Subscript out of Range"
    By sev979 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 09:55 AM
  2. Array In VBA Formula ... "Subscript out of range"
    By kestefon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-07-2013, 04:07 AM
  3. Error msgs: "Object varible or with block variable not set"; "subscript out of range"
    By menyanthe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-26-2009, 04:58 PM
  4. [SOLVED] "Subscript out of range" error for: Workbooks("Test1.xls").Save
    By Just12341234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2005, 11:05 AM
  5. FileCopy Command Giving "Subscript Out of Range" Error Message
    By Jim Hagan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2005, 02:05 PM

Tags for this Thread

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