+ Reply to Thread
Results 1 to 8 of 8

New to arrays-how do I build new array while filtering first array?

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Question New to arrays-how do I build new array while filtering first array?

    Please see code below. I am struggling with building the new array for the listbox on the form.

    (Unless it would be faster/more efficient just to filter & delete values in the original array?? But I don't know how/if this is even possible)

    Please Login or Register  to view this content.
    Last edited by mc84excel; 05-02-2013 at 06:10 PM. Reason: Correct code xlRight to xlToRight
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    I think this is closer but I am still missing something.

    (Extract only this time)
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Unhappy Re: New to arrays-how do I build new array while filtering first array?

    stumped. Runtime error 9. Below is the latest version... (If you add a exit sub line just before the code building the new array, the error message doesn't appear. So it's definitely the sub array that's causing the issue)

    Please Login or Register  to view this content.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: New to arrays-how do I build new array while filtering first array?

    if you have a 2D array you can only resize the last dimension if you use redim preserve
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    Quote Originally Posted by JosephP View Post
    if you have a 2D array you can only resize the last dimension if you use redim preserve
    I am new to arrays. However I am already using redim preserve in the code in post #3.

    So where am I going wrong?

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    I had a friend have a go at the code and here is his version. However I still get a runtime error 9 subscript out of range message.

    I have stepped through the code and it all runs smoothly, checking through the main array etc. UNTIL it reaches a match. As soon as VBA goes from this line:
    Please Login or Register  to view this content.
    to this line
    Please Login or Register  to view this content.
    it gets the error message. Why?

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: New to arrays-how do I build new array while filtering first array?

    Solved.

    Thank you all for the help
    Last edited by mc84excel; 05-02-2013 at 07:31 PM.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: New to arrays-how do I build new array while filtering first array?

    for others' benefit it would be good if you posted the answer :-)

    I didn't review the code but suspect you needed varNewArray(0, UBound(varNewArray, 2)) = varMainArray(i, 1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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