+ Reply to Thread
Results 1 to 9 of 9

Assigning range to 2D array: subscript out of range

  1. #1
    Forum Contributor
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    126

    Question Assigning range to 2D array: subscript out of range

    Objective: to load 700 X 2 range into an array. This array is thereafter loaded to a 2 column listbox which will be used to select the row in listbox and copy selected listbox value into a particular cell.

    Error line is in red. This code is within Userform_initialize procedure.

    What has been tried and tested?

    1. Using Immediate Window, I have found values for prj(1,1), prj(2,1) etc. to be valid and consistent with table.

    2. Ubound and Lbound is correct for row and col's start and end.


    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning range to 2D array: subscript out of range

    As you have declared dataArr as a one based array it should be
    Please Login or Register  to view this content.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Assigning range to 2D array: subscript out of range

    Why are you looping rather than just assigning the range to a variant, or even straight to the List property of the control?
    Rory

  4. #4
    Forum Contributor
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    126

    Re: Assigning range to 2D array: subscript out of range

    Quote Originally Posted by Fluff13 View Post
    As you have declared dataArr as a one based array it should be
    Please Login or Register  to view this content.
    Aye, this was the problem after all. What do you mean by one based array? Also, I found using Dim dataArr instead of Dim dataArr() seems to done the trick, I am not sure but it seems confusing to me as I learnt arrays need to be declared with parenthesis.

    This was the most nonsensical thing I have learnt today:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning range to 2D array: subscript out of range

    This should work
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    126
    Quote Originally Posted by Fluff13 View Post
    This should work
    Please Login or Register  to view this content.

    Yes it worked. Please explain difference in prjs.value and assigning prjs to another variable.

    Do you know of any good resource on this, I may need to read up to clarify my basics. Much appreciated.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning range to 2D array: subscript out of range

    When assigning a range to an array it will automatically use the range value, but not when assigning it to the List property of a listbox. Although I have no idea why.

  8. #8
    Forum Contributor
    Join Date
    06-20-2021
    Location
    UK
    MS-Off Ver
    2016
    Posts
    126

    Re: Assigning range to 2D array: subscript out of range

    Quote Originally Posted by Fluff13 View Post
    When assigning a range to an array it will automatically use the range value, but not when assigning it to the List property of a listbox. Although I have no idea why.
    Thanks solved.

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Assigning range to 2D array: subscript out of range

    Glad to help & thanks for the feedback.

+ 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. [SOLVED] Named range into array using VBA - Subscript out of range
    By Pak Mariman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-01-2021, 06:31 AM
  2. [SOLVED] Array Subscript Out of Range
    By js0873 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-19-2017, 09:26 AM
  3. [SOLVED] Subscript out of Range - But Array is in different workbook
    By CRIMEDOG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2015, 02:29 PM
  4. [SOLVED] Subscript out of range - Array empty
    By sperry2565 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2014, 02:19 PM
  5. [SOLVED] Subscript out of range on Array
    By ZiadzExcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2012, 11:35 AM
  6. Subscript out of range error while populating array from range.
    By Aussiexile in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-04-2010, 02:22 AM
  7. Subscript out of range - multi array
    By Buffyslay in forum Excel General
    Replies: 6
    Last Post: 02-15-2006, 10:15 AM

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