+ Reply to Thread
Results 1 to 16 of 16

Trying to save rows to an array to fill a listbox

  1. #1
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Question Trying to save rows to an array to fill a listbox

    I would like to find rows (in my spreadsheet table) that pass my If statement
    Add each of those rows that pass, to an array
    and use that array to fill a listbox (Each row on a new line/index)

    I can't seem to quite get everything to fit together between the "Type Mismatch" and the "Subscript out of range" errors. I suspect the problem is somewhere in the array declarations but as I mentioned, I've been unable to find a successful combination but another issue may involve my attempting to display multiple cells as each Listbox item.

    I've tried both reading the row cell by cell (which I've currently commented out)
    and reading the entire row (which may NOT work since I expect it will squish everything together and make it unreadable when displayed as a listbox choice).

    The latest version of the subroutine included below. My hope is that someone here can point me in the right direction. As ALWAYS, guidance would be greatly appreciated.

    Please Login or Register  to view this content.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Trying to save rows to an array to fill a listbox

    I did not look at all your code, nor did I test run it, however, which line is throwing these errors?

    Type Mismatch

    Subscript out of range

    keep in mind that you CANNOT throw entire rows into array elements, 1 dimensional or 2 dimensional. not that I know of anyway. however, you can do other things:

    1) concat all the cell data from the entire row into 1 string and throw it to arrays.
    2) throw each cell's contents into different array dimensions. but this would become seriously complex.

  3. #3
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Trying to save rows to an array to fill a listbox

    A copy of your worksheet would be a great help.
    Help us to help you.
    See big yellow banner at the head of the page.
    torachan.

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Trying to save rows to an array to fill a listbox

    Small amount of brain fog today, then suddenly realised I had seen something familiar recently
    If the app is still similar i.e.(your form appears on the blank sheet that has your launch button).
    This would be your active page and not containing any data that your are searching, hence subscript out of range.
    Rather than trying to construct another array think laterally, you have a successful array, remove from it those rows that do not meet the criteria.
    You could also use a filter on the table and populate the listbox with the visible rows from the table.
    With an up to date workbook to work with an example can be given.
    torachan.

  5. #5
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: Trying to save rows to an array to fill a listbox

    YES! It should look very familiar to you. I made myself a test copy of your code so that I could experiment with it and try to learn how it works. I was able to separate out the "m1:" or "m2:" etc. from the wives first names into a new textbox but collecting the data and adding it into a new listbox has stumped me. I've tried a few different approaches and the search for "sons" nearly works except that each field is on a different line in the listbox instead of all fields for the match on the same line as you did in lbo1. I considered attempting to contact you directly but decided that you'd already been more than generous with your time and I shouldn't bother you.

    I've been selecting the first name, Peter, to test with because he had both brothers and sons to try to gather for my display. Oh... on a similar note, I could never find where the array Nary was declared.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: Trying to save rows to an array to fill a listbox

    Thanks Adam I was only aiming to grab the first 6 columns of the row. As for the errors, I kept changing the lines that had errors in a blind effort to resolve them. Torachan is familiar with the original code that I was attempting to follow but obviously did not understand well enough to duplicate the process.

  7. #7
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Trying to save rows to an array to fill a listbox

    Well saying that someone doesn't understand your work well enough to duplicate the process isn't a nice thing to say but that's not a problem. So what exactly are you asking? Are you asking for my help or his or both? Or rather, do you even need more assistance at this point?

  8. #8
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: Trying to save rows to an array to fill a listbox

    Adam, I'm very sorry. It was never my intention to offend anyone. I'm just interested in learning how to combine the use of controls with tables. Please feel free to disregard this problem.

  9. #9
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Trying to save rows to an array to fill a listbox

    Well my last post didn't mean the same thing and I'm sure anybody welcomes you to ask any question you want on this board. Including me and I'll try to answer anything I can. But if you don't want my help further that's fine with me too but you can continue to ask questions in this thread because I know there are many other professionals looking at it besides me.

  10. #10
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Trying to save rows to an array to fill a listbox

    @Adam, you really must knock the chip off your shoulder.
    re-read post #6, you have completely read out of context @imaquila was inferring she did not understand NOT that you did not understand.
    Maybe an apology for your brusque reply would not go amiss.
    torachan.

  11. #11
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Trying to save rows to an array to fill a listbox

    @imaquila, I see there has been no solution posted.
    If you can explain the actual breakdown of the 'branch' code so that I fully understand it.
    I will come back with a solution.
    torachan.

  12. #12
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: Trying to save rows to an array to fill a listbox

    Thanks so much for your time!

    "A" represents the earliest known ancestor in a family branch and each additional character represents a generation descended from "A". So the first character added to "A" represents a child of "A" ie. A1, A2, and A3
    If "A2" were to have children, they would be identified as A21, A22 etc. So a Branch ID of A3112 is a means to trace the direct parental line of the "2" (on the far right) starting with their father "1" (to the left) who's father in turn was "1" (of the previous generation), who's father was "3", who's father was "A".

    Your interest is appreciated!

  13. #13
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Trying to save rows to an array to fill a listbox

    Tested my one old brain cell to the limit.
    I think I finally grasped the permutations.
    I have used filtering to a hidden sheet, then used that to populate the second listbox rather than building the array, it was simpler.
    The only oddity that I gave up on was the group of brothers also includes the search subject person.
    torachan.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: Trying to save rows to an array to fill a listbox

    torchan, Thank you very much for your assistance. I've been looking it over, and I think I'm going to have to accept that I will not going be able to understand exactly how you worked this magic using tables. Maybe someday, I'll come across a book or article about working with tables but until then, I'll content myself with the knowing that Tables are useful for MUCH more than just sorting!

    Thanks so MUCH for your help.

  15. #15
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: Trying to save rows to an array to fill a listbox

    You are welcome.
    Below a couple of links that prove very useful.

  16. #16
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 365
    Posts
    83

    Re: Trying to save rows to an array to fill a listbox

    These look to be GREAT sources of information! Thank you so MUCH for taking the time to share them! I'm looking forward to studying these pages and see what I can absorb from them!

    I wish I could give you more stars!!!

+ 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. Help with a Dynamic array in VBA (to fill a listbox)
    By yoursamrit2000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-28-2015, 02:51 PM
  2. [SOLVED] declare array and fill rows and columns
    By CostCare in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2015, 10:20 AM
  3. Replies: 3
    Last Post: 10-05-2014, 11:48 AM
  4. Replies: 12
    Last Post: 08-28-2012, 07:09 AM
  5. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  6. Fill a LISTBOX from a range, WHITOUT BLANK ROWS
    By aleebaba in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 11-07-2011, 03:06 AM
  7. Fill listbox with Array of folders
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-26-2010, 01:13 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