using a listbox or combobox to refer to columns or sheets

Hello,

I've been working on a userform for a few days now (it's the 1st I've ever attempted) and can't seem to get all the pieces right. theDude helped make most of the form functional (thanks!) but I've hit upon another snag & was hoping that someone... anyone... might be able to point me in the right direction.

Basically I'm looking for a way to use multiple-listbox &/or combobox selections to pick specific columns &/or sheets for use later on. I know how to enter the data I want into the boxes and to have the user select one or more entries per box (I can even show them their listbox selections via msgbox). I just don't know how to turn those selections into column or sheet references.

Ok so, first problem: I have a listbox that I've populated with the contents of cells A1:S1 which are actually the headers for columns A:S (of undetermined length). Anyhow, I'd like for users to pick multiple headers and for the columns they 'represent' to be bound together into an array for use later on. Here's the relevant code:

Please Login or Register  to view this content.

I'm fairly sure I'll need to Set each selection to the range they represent, possibly so that simply extends to the 'last row' of each column. Perhaps I need to name each column/range too. I just don't know. My mind has turned to mush.

Ok so I'd also like to put a dropdown list (=combobox I suppose) BEFORE the listbox to allow the user to pick which sheet they'd like those columns to come from. You see I've got 4 nearly identical sheets in this one workbook (W1, W2, W3, W4); they've all got the same number of rows & columns (+ the same headers for each), but the contents of many non-header cells will differ between sheets. So I've entered the "W1"-"W4" titles into a combobox and I'd now like to assign a sheet to each. I'd also like the selected sheet/title (whatever it turns out to be) to have a generic name, like "ChosenSheet" that I can then insert throughout the rest of the code whenever I need to refer to the dataset that the user wants to use. The annoying thing is that I can plan this all out in my head but I can't find the commands/tools to make it work. Here's the simple combobox code so far:

Please Login or Register  to view this content.

It's obviously not very advanced but I've only just started on it. Oh, and if it'd make life much easier, I'll happily use another listbox instead of a combobox on this form (it's just that the combobox seems more applicable in this case).

Can anyone suggest any methods for associating each selection to a larger array or sheet? Would this be a situation where I'd use 'data binding'? I don't really understand it or know what it is, I just ran across a mention of it yesterday & thought it miiiiight apply but I can't find clear directions on how or why to do it... at least none that fit the control/userform situation I'm in &/or that would work in VBA for excel.
Any ideas would be appreciated. Thx.

Update: I figured out how to identify & then use the selected sheet. I ended up using the relatively obvious (tho crude) solution, even though I'd been hoping to find a better way. Oh well, this code might be rather inefficient & ugly, but at least it works:

Please Login or Register  to view this content.

However I'm still confused by the problem of 'sticking' sequential rows & columns together to form a large results array. Sure I suppose I could lumber through each cell in turn but that seems like a pretty pathetic & time-wasting option (not to mention that it's probably not possibly anyway since I won't know how many columns were picked ahead of time).
I've been searching for ideas online for ages now but really can't find any answers that apply to columns rather than individual values/entries. I suppose I could also do like I did for the sheets & individually assign each selection to a specific column, but I'm dealing with 20 different columns and would prefer to find a better way.
Hope someone can offer even just a vague suggestion. Thx.

Fern,

I need more clarification on what you plan to do with the columns selected by the user; are you going to manipulate the data in the cells in the column(s) selected, are going to feed data to the column(s) selected, are you going to format the cells, etc.

You can use a multidimesional array. Here's Visual Basic Help info:

In Visual Basic, you can declare arrays with up to 60 dimensions. For example, the following statement declares a 2-dimensional, 5-by-10 array.

Dim sngMulti(1 To 5, 1 To 10) As Single

If you think of the array as a matrix, the first argument represents the rows and the second argument represents the columns.

Use nested For...Next statements to process multidimensional arrays. The following procedure fills a two-dimensional array with Single values.

Sub FillArrayMulti()
Dim intI As Integer, intJ As Integer
Dim sngMulti(1 To 5, 1 To 10) As Single

' Fill array with values.
For intI = 1 To 5
For intJ = 1 To 10
sngMulti(intI, intJ) = intI * intJ
Debug.Print sngMulti(intI, intJ)
Next intJ
Next intI
End Sub

Hope this helps,
theDude

Hey,

To answer your first question: Before I decided to let the user pick which columns to include, I was only going to let them choose how many to include (e.g. 10) - so it was up to them to re-format the datasheet so it contained only the columns they wanted... an annoying step when you're running the macro on dozens of data combinations. Anyway, that original code had an array containing the raw data ("OrigDataArr"), and then another ("TempDataArr") from which the applicable ROWS would be picked (that's the whole point of the macro... to copy 70/200 rows to a results sheet/array BUT only for certain (e.g. 10) columns). Then the main part of the macro would run, the rows would be picked, and the overlap between the macro-picked rows & user-selected columns would be copied to a 3rd array ("SubsampledDataArr") which would then be 'pasted'/transferred onto the designated results sheet.
Anyway, what I'd now like is to set this up so those 10 selected columns don't have to be right next to each other in the original dataset - so a user could select columns A, B, D, H, L, etc instead of being forced to get all of A:L, even if they don't want them all. So if the listbox selections could be used to pare down the 1st dataset array into a 2nd array containing the same number of ROWS but fewer COLUMNS (i.e. only those selected in the listbox), then the rest of my macro should still work properly.

As an FYI, here's most of the array coding in my "Main" macro:

Please Login or Register  to view this content.

I hope that's enough info for you. I'll return to playing with arrays. Thanks for the info so far - hopefully you can point me in the right direction again, now that you have a better idea of what my 'plan' is.
Thanks so much!

One more thing: The part that's really confusing me is that I need to build this array (a) out of columns that aren't necessarily next to each other on the original datasheet, and (b) more frustratingly, their number & location won't be known until the user selects them from the listbox. You see, I know how to build the array from known columns in a known order...

Please Login or Register  to view this content.

...but not if I don't know exactly which worksheet range the selected columns will come from or which array position they're going to...

Ok, well I've been playing around, trying to find ways to get the macro to do what I want, even if it's ugly. This new code doesn't entirely work but I think I'm getting closer to an answer. Should I keep fumbling through in the direction I'm going or do you have a better suggestion?

The new attempt is set up to run in any workbook & doesn't rely on definitions from earlier in the code (they've been assigned constant #s for the time being), apart from the listbox being populated by values from Worksheets("Sheet1").Range("A1:W1"):

Please Login or Register  to view this content.

Efficient? No. Pretty? Not a chance. Working? Mmm, no, not really.
Current problems with it include the fact that the array seems to stay empty despite using ReDim Preserve, and that I don't think the If/Then loops are particularly correct (too many unnecessary layers to run).
Suggestions eagerly welcomed.

Hi Fern!

I took me longer than I wanted, but I think I have a solution for you. I set up a test userform with two listboxes. Listbox2 gets auto-populated with "WD1, 2, 3..." worksheet names when the form is opened for the user to make a single selection from; its' on_click procedure enables listbox1 (allows multiple selections) and populates it with all column numbers in the used range of the worksheet selection from listbox1.

When the user selects columns from the list and clicks OK, it loops through the selection(s) in the listbox and then creates a new worksheet and populates it (starting in column A) with data from all rows of the column(s) selected.

I left out some error checking and row selection stuff, but based on your sample code, I'm confident you can make the necessary adjustments...Here's the code for the UserForm and its' objects:

------------------------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
' Disable listbox1 until user selects worksheet from listbox2
ListBox1.Enabled = False
ListBox1.Clear
' Initialize listbox2 with worksheet names for user to select only one
For i = 0 To 3
ListBox2.AddItem "WD" & (ListBox2.ListCount + 1)
Next i
End Sub
-------------------------------------------------------------------------------------------------------
Private Sub ListBox2_Click()
Dim UserSheet
' Get name of user-selected worksheet to grab data from...
UserSheet = ListBox2.Text
' Initialize listbox1...
ListBox1.Clear
' Load listbox1 w/ all columns from user-selected sheet...
For i = 0 To ActiveWorkbook.Worksheets(UserSheet).UsedRange.Columns.Count - 1
ListBox1.AddItem "Column " & (ListBox1.ListCount + 1)
Next i
' Allow multiple column selections in listbox1...
ListBox1.MultiSelect = fmMultiSelectExtended
' Enable the listbox and set focus...
ListBox1.Enabled = True
ListBox1.SetFocus
End Sub

-------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim intI As Integer, userCols As Integer, userRows As Integer
Dim a(256) As Integer
Dim UserSheet, myKey, colNum

' Hide user form...
UserForm1.Hide
' Get user-selected sheet name...
UserSheet = ListBox2.Text
' Get row count of user user-selected sheet for use in data fill...
userRows = ActiveWorkbook.Worksheets(UserSheet).UsedRange.Rows.Count
' Create new sheet to fill with user-selected data...
Set NewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
NewSheet.Name = "UserSelections"
' Loop through the listbox selections to get columns to read...
userCols = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
userCols = userCols + 1
' Strip column number from description...
myKey = InStr(1, ListBox1.List(i), " ")
colNum = Mid(ListBox1.List(i), myKey + 1, 3)
' Add column number to the array...
a(i) = lineItem
End If
Next i
j = 0
newCol = 0
skipCount = 0
' Loop through array and populate new sheet with values from user selection(s)
Do Until j = ListBox1.ListCount
' Get only user-selected column data...
If a(j) <> 0 Then
newCol = newCol + 1
' Fill new workseet with user-selected column values.
For intI = 1 To userRows
With Worksheets(NewSheet.Name).Cells(intI, newCol)
.Value = Worksheets(UserSheet).Cells(intI, a(j)).Value
End With
Next intI
j = j + 1
Else
' Skip columns not selected...
skipCount = skipCount + 1
newCol = j - (skipCount - 1)
j = j + 1
End If
Loop
End Sub
-------------------------------------------------------------------------------------------------------
Hope this helps,
theDude

Hi All!

I apologiize to anyone who grabeed my code in the last post. I was cleaning it up and forgot to make one final change (in BOLD below); here is the revised code:

----------------------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
' Disable listbox1 until user selects worksheet from listbox2
ListBox1.Enabled = False
ListBox1.Clear
' Initialize listbox2 with worksheet names for user to select only one
For i = 0 To 3
ListBox2.AddItem "WD" & (ListBox2.ListCount + 1)
Next i
End Sub
----------------------------------------------------------------------------------------------------
Private Sub ListBox2_Click()
Dim UserSheet
' Get name of user-selected worksheet to grab data from...
UserSheet = ListBox2.Text
' Initialize listbox1...
ListBox1.Clear
' Load listbox1 w/ all columns from user-selected sheet...
For i = 0 To ActiveWorkbook.Worksheets(UserSheet).UsedRange.Columns.Count - 1
ListBox1.AddItem "Column " & (ListBox1.ListCount + 1)
Next i
' Allow multiple coulumn selections in listbox1...
ListBox1.MultiSelect = fmMultiSelectExtended
' Enable the listbox and set focus...
ListBox1.Enabled = True
ListBox1.SetFocus
End Sub
----------------------------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
Dim intI As Integer, userCols As Integer, userRows As Integer
Dim a(256) As Integer, i As Integer, j As Integer
Dim newCol As Integer, skipCount As Integer
Dim UserSheet, myKey, colNum

' Hide user form...
UserForm1.Hide
' Get user-selected sheet name...
UserSheet = ListBox2.Text
' Get row count of user user-selected sheet for use in data fill...
userRows = ActiveWorkbook.Worksheets(UserSheet).UsedRange.Rows.Count
' Create new sheet to fill with user-selected data...
Set NewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
NewSheet.Name = "UserSelections"
' Loop through the listbox selections to get columns to read...
userCols = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
userCols = userCols + 1
' Strip column number from description...
myKey = InStr(1, ListBox1.List(i), " ")
colNum = Mid(ListBox1.List(i), myKey + 1, 3)
' Add column number to the array...
a(i) = colNum
End If
Next i
j = 0
newCol = 0
skipCount = 0
' Loop through array and populate new sheet with values from user selection(s)
Do Until j = ListBox1.ListCount
' Get only user-selected column data...
If a(j) <> 0 Then
newCol = newCol + 1
' Fill new workseet with user-selected column values.
For intI = 1 To userRows
With Worksheets(NewSheet.Name).Cells(intI, newCol)
.Value = Worksheets(UserSheet).Cells(intI, a(j)).Value
End With
Next intI
j = j + 1
Else
' Skip columns not selected...
skipCount = skipCount + 1
newCol = j - (skipCount - 1)
j = j + 1
End If
Loop
End Sub

Once again, my apologies for the inconvenience!
theDude

Oh you beautiful beautiful Dude! I've just run your code on a test worksheet and it works like a DREAM! Thank you SO much. I've got to run out for the rest of the evening & so won't be able to tell you how it fits into my real code until tomorrow... but if my initial attempts are any indication, I know I'm going to be thrilled with the results. I'll let you know how it goes tomorrow.
And even if it doesn't work,

THANK YOU THANK YOU THANK YOU!

You're fabulous! And I'm SO grateful!

Sigh.. so of course there's a problem.

I was so excited by your code last night that I didn't notice it was creating a worksheet out of the columns selected, and that that particular part of the procedure wasn't as 'removeable' from the rest of it as I might have hoped. It does a FANTASTIC job of pulling out ONLY the selected columns but I'm not sure how to integrate it into the rest of my code so that it effectively replaces ONLY the "TempDataArr" array that you can see in the third cluster of code I posted in this thread. I'd rather it didn't create a visible sheet so soon since there's still the rest of the "Main" macro to run & I'm worried that if I change that code so it removes rows from a visible sheet rather than a hidden array, it'll run much slower & take up much more memory.
I'm in the process of playing around with the codes so they'll fit together better, but would still be grateful for ideas on how to integrate your coding into mine since, as I've said before, arrays (& VB!!) aren't my strong suit.

Some other little questions:
(1) userCols doesn't seem like it's really used for anything - I assume it counts the number of selected columns, but that info is never used anywhere else... Am I missing something?
(2) if I alter your code so that my ListBox1 is populated by the contents of row1, what else do I need to change since your version refers to them as column1, column2 etc? This is particularly important for the line "a(i) =..."
(3) if the contents of ListBox1 always start from column A (i.e. A1:S1, not D1:S1), the couldn't I just use the ListBox1.ListCount value (=i), instead of "colNum", when definine the column number for each selected column??

I think that's it. I'm going to give this another try, and hopefully I can combine it all without too much more trouble. Thanks for all your help!
I'll try to post my updated version in an hour or so, whether I solve the 'translation issues' or not.

Fern,

See my replies to your questions and revised code:

Some other little questions:
(1) userCols doesn't seem like it's really used for anything - I assume it counts the number of selected columns, but that info is never used anywhere else... Am I missing something?
Answer: You’re correct; it was code that I was testing that I ended up not using and forgot to clean it up. See my new code below where I use it…

(2) if I alter your code so that my ListBox1 is populated by the contents of row1, what else do I need to change since your version refers to them as column1, column2 etc? This is particularly important for the line "a(i) =..."
Answer: See my new code below …

(3) if the contents of ListBox1 always start from column A (i.e. A1:S1, not D1:S1), the couldn't I just use the ListBox1.ListCount value (=i), instead of "colNum", when definine the column number for each selected column??
Answer: Unfortunately you can’t because ListCount is the TOTAL number of items in the list, not the index of the selected item in the list. Also, you can’t use the ListIndex property with multi-select listboxes, so I just added a number to the items in the list that I could strip out to use as the column reference. Based on your request I have redone the code to load a temp array, then unload it to a new worksheet :

Private Sub CommandButton1_Click()
Dim intI As Integer, userCols As Integer, userRows As Integer
Dim a(256) As Integer, i As Integer, j As Integer, k As Integer
Dim newCol As Integer, skipCount As Integer
Dim UserSheet, myKey, colNum

'Hide user form...
UserForm1.Hide
' Get user-selected sheet name...
UserSheet = ListBox2.Text
' Get row count of user user-selected sheet for use in data fill...
userRows = ActiveWorkbook.Worksheets(UserSheet).UsedRange.Rows.Count
' Loop through the listbox selections to get columns to read...
userCols = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
' add 1 to the index since ListBoxes are indexed starting at 0
userCols = i + 1
' Add column number to the array...
a(i) = userCols
End If
Next i

' Loop through array to get count of user-selected
' columns to dimension Temp array...
Dim colCount
k = 0
colCount = 0
Do Until k = ListBox1.ListCount
' Get only user-selected column data...
If a(k) <> 0 Then
colCount = colCount + 1
End If
k = k + 1
Loop

' Create multi-dimensional Temp array based on user selections...
Dim Temp() As Variant
ReDim Temp(userRows, colCount) As Variant

' Load Temp array with data...
j = 0
newCol = 0
skipCount = 0
' Loop through array 'a' and populate Temp array with values from user selection(s)
Do Until j = ListBox1.ListCount
' Get only user-selected column data...
If a(j) <> 0 Then
newCol = newCol + 1
For x = 1 To userRows
Temp(x, newCol) = Worksheets(UserSheet).Cells(x, a(j)).Value
Next x
j = j + 1
Else
' Skip columns not selected...
skipCount = skipCount + 1
newCol = j - (skipCount - 1)
j = j + 1
End If
Loop

' Turn off screen updating (keep things transparent to the user)...
Application.ScreenUpdating = False

' Display message in status bar at bottom of Excel window...
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Creating new worksheet " & NewSheet.Name & " and loading it with data; please wait..."
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime


' Create new sheet to fill with user-selected data...
Set NewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
NewSheet.Name = "UserSelections"

' Fill new worksheet with data from Temp array...
For x = 1 To userRows
For y = 1 To colCount
With Worksheets(NewSheet.Name).Cells(x, y)
.Value = Temp(x, y)
End With
Next y
Next x

' Reset screen updating and status bar
Application.ScreenUpdating = True ' Turn on screen updating
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar

End Sub

Hope this helps,
theDude
P.S. - Check your private messages

One step forward, two steps back. How frustrating.

I've been trying to incorporate theDude's great code for most of the day (along with ideas picked up elsewhere) but can't seem to get these #$@*&^! arrays to work correctly. The closest I came was when data WAS being copied & pasted, and some version of my "Main" macro was being run, but the resulting sheet didn't look anything like it was supposed to (the wrong rows were being moved etc etc). So rather than asking a question about & having you labor over only one (simplified) part of the code at a time, only to find -after many long hours of struggle- that I can't combine your solution into my larger code without horrible repercussions elsewhere, I figured it might be easiest (in the long run) if I were to simply post the whole monster at once. That way you could see how it's all supposed to hang together, what a specific array is used for, and where its components come from & go to. And, in case that's still not enough info, I'm also attaching a simplified copy of my workbook & whole code so you'll be able to see precisely where & why it's all going horribly wrong. I'd also be happy to email a non-zip copy to anyone who prefers to get that way...

Please Login or Register  to view this content.

You see? It's far too massive & complicated for a newbie like me to fully understand, esp once I start dealing with multi-dimensional arrays.
In terms of where the problems are occurring, most seem to revolve around having 2 vs 3 vs 4 dimensions defined in the brackets. My coding for "Main" (& in my older arrays) used 3, but theDude's only use 2. I've tried adding, subtracting, & messing about with just about everything, but haven't had any success yet. Would using an array (of column index values) inside the TempDataArr array dimensions be one way around this, or do I have to find some other solutions?? Perhaps Intercept would work instead?
The 0 vs 1 bases (for Cells/Ranges vs Arrays) have also been a real hassle, so don't be surprised if there are lots of mistakes there too. I also apologise for obvious things I may have overlooked or had correct earlier (but don't now). I blame those on my growing frustration with these arrays & the frantic (but useless) changes I've been making to my code over the last few hours, often without undoing unsuccessful changes before going on to make new ones.

Anyway, I'm tired & sore from sitting in front of this evil soul-sucking machine for more than twelve solid hours today, so I'm going to head off to sleep. I apologise for the mega-post but I figured it was one sure fire way to save everyone time & energy, since ping-pong posting hasn't been entirely successful.

Thank you for ALL of your help so far and for any additional generosity you can spare me. You guys rock (especially theDude)!

Fern,

The Temp array in my sample is not two-dimensions (as you stated); it morphs from no dimensions to a multi-dimensional array that varies based on user input. Think of an array as an invisible empty spreadsheet (an Excel spreadsheet has a maximum of 65,536 rows by 256 columns); the difference is the array has no upper limit on the number of rows and a max of just 60 columns.

When declaring an array, you can explicitly specify the number of rows and columns it will contain like this:

Dim TempDataArr(10, 2) As Variant - In this case, the array will have ten rows and two columns. If it was a spreadsheet, this translates to cells A1: B10.

In your application, you don't know how many dimensions you will need until the user makes 2 selections in the UserForm:

1) The selection in ListBox2 (the worksheet to grab data from) will determine the number of rows for the array from the 'UsedRange.Rows.Count' property)...this gets assigned to the 'numRows' variable.

2) The selection(s) in ListBox1 - the specifc 'column(s)' selected by the user to grab data from the worksheet selected in ListBox2. This is limited only by the number of items in ListBox1...)...this eventually gets assigned to the 'colCount' variable.

So, you need to declare your array initially with NO dimensions like this:

Dim TempDataArr() As Variant

AFTER you have calculated the number of dimensions based on user input, you then re-dimension the array:

ReDim TempDataArr(numRows, colCount) As Variant

You then populate the array with data based on the user's selections from the form...

As far as setting the 'base' option for the array, think of it as an index (or key) for the row of the array. VB defaults the key to the first row to be zero (Option Base 0) unless you explicitly declare it to be 1 (Option Base 1). Because the default base is 0, the Option Base statement is never required. If used, the statement must appear in a module before any procedures. Option Base can appear only once in a module and must precede array declarations that include dimensions. The Option Base statement only affects the lower bound of arrays in the module where the statement is located. I always let it default to zero and adjust with code as needed to translate the array index I need to row/column indexes in spreadsheets (they are always in 'base' option 1).

I'll take a closer look at your code and see if there is something I can spot and try to help with.

TheDude

Oh thank you theDude! Your explanation confirms a lot of what I was assuming & clarifies much of the rest. The part about base numbers was very helpful, altho I'lll probably still get mixed up when trying to convert cell addresses (base 1?) to arrays (base 0). Oh well, I'll get it right eventually - debugging is great for trial & error coding

However I'm still not sure if you understood the gist of my question about 2 vs 3 vs 4 'dimensions' (note: 'dimensions' was the wrong word - 'parameters' is more accurate). If you refer to the "Main" part of my macro, you'll see that it defines TempDataArr using more than just two parameters/subscripts: generally iNumSubsamples, iNumPopulations, (iMaxStudies), iFields/colCount. That's because the "Main" is used to pull out one study/row (from 161 total) per population (x71pops) & to collapse those into a single subsample (of 71 rows), and then to repeat that process iNumSubsamples times while sticking each subsepquent sample on the results sheet below the last used row. So b/c of that eventual requirement, the TempDataArr that you developed can't ONLY be defined via numRows & colCount.
Perhaps it first needs to be called TempDataArr1 and to contain only the columns selected via ListBox1 (=colCount, I think) plus ALL the rows (=161 = TotalRows, I think). Then it could be copied?/redefined?/cloned? into TempDataArr2 which would use the commands from "Main" to whittle it down to colCount (=ListBox1 value) x userRows (=71 = iNumPopulations) x iNumSubsamples (=TextBox1 value). I don't know... maybe that's far too simplistic or too complicated an answer for the matter at hand.

This is all so confusing to me, esp since I can't keep track of which changes are new, which are old, which are guesses, which work, & which don't.
I'll keep toiling away on this. Thank you for offering to help - I'm extremely grateful! No worries if it takes you a while to reply - I know how frustrating this code can be...

the Dude? You still around? Have you come up with any ideas for me? Are you too busy to work on it? Did you get bored & move on to other puzzles?
In case you're still interested, I'm still very stuck on the same section of code. In fact, I got so frustrated that I started working on another macro entirely, with the hope that you'd have more luck with the first than I had. I don't know what else I can do with this monster. You (or anyone else who's generous enough to help) are the great hope in this epic story. Please let me know if you need any other info before tackling this or if there's anything I can do to sort this out myself (altho' I'm sick of trial-&-error coding, so specific advice would be great).

Like I've said before, every little bit is MUCH appreciated. Thanks so much!

fern,

I got !@#%-hammered with tons of other stuff and haven't had the time, but I'll take a look tonight...

theDude

Sorry for the annoying post but I wasn't sure if you'd forgotten, were avoiding, or had just gotten too busy to reply. Don't worry about the delay or think that I'm getting impatient - I was just looking for an update in case I needed to ask for help elsewhere. I'm very familiar with heavy workloads and don't want to add to your stress, esp since you're only helping me out of the goodness of your heart (&/or pity). Good luck with your work and thx again!

fern,

After a review of the sample data & code you provided, I have a some questions:

In the sample file, worksheets W1-W4 are clones of each other, so I don't understand why the user would have to select one of them from the UserForm to begin with? Also, they contain only 51 rows of data, yet in a previous post to this thread, you refer to 161 rows, which don't exist in the worksheets that the TempDataArray is populated from...you also refer to 71 rows and 71 populations:

"If you refer to the "Main" part of my macro, you'll see that it defines TempDataArr using more than just two parameters/subscripts: generally iNumSubsamples, iNumPopulations, (iMaxStudies), iFields/colCount. That's because the "Main" is used to pull out one study/row (from 161 total) per population (x71pops) & to collapse those into a single subsample (of 71 rows), and then to repeat that process iNumSubsamples times while sticking each subsepquent sample on the results sheet below the last used row. So b/c of that eventual requirement, the TempDataArr that you developed can't ONLY be defined via numRows & colCount.
Perhaps it first needs to be called TempDataArr1 and to contain only the columns selected via ListBox1 (=colCount, I think) plus ALL the rows (=161 = TotalRows, I think)..."

I'm confused where these numbers are coming from...

In the code from the file:

TempDataArray is dimensioned like this:
ReDim TempDataArr(TotalRows, colCount) As Variant)
but its' dimensions are different than the SubsampledDataArr and then an attempt is made to try to feed data to the SubsampledDataArr:
SubsampledDataArr(ss, p, c) = TempDataArr(p, RandNumbChosen, c) where you changed the dimensions of the TempDataArray without redimensioning and filling it with data it beforehand.

You also use the variable 'iRow' in a conditional statement:
If iRow > 30000 Then...
but 'iRow' is never valued prior to its' use.

To try to hopefully understand what you need, I checked other posts you made and found one in particular (...macro to get stratified random sample of rows?...) that I think is what you're trying to accomplish with this thread?

I really want to help you out, but maybe we should talk because this is turning into a web version of a bad reality TV show...

theDude

Hi again theDude,

Yes, I agree - this is getting rather complicated isn't it? How were you suggesting that we 'talk'?

Well, back to the problem at hand...

In the sample file, worksheets W1-W4 are clones of each other

Actually they're not supposed to be & I don't know why they came out that way. They should contain the same # of columns & rows, the same headers, and the same contents in columns A:F. However the data in columns >F would differ between sheets. In actual fact, each row would be weighted by a difference set of constants depending on the sheet. Sorry for that - I was trying to simplify my real dataset & must have copied W1 into all 4 sheets rather than doing each separately. Why don't you just throw random values in there for the time being since the data itself doesn't matter? Alternatively I could just attach another (more accurate) copy of the file for you...(?)

Also, they contain only 51 rows of data, yet in a previous post to this thread, you refer to 161 rows

Sorry - that was a miscommunication on my part plus another example of simplifying my dataset (from 161rx21c down to 51rx11c - tho that shouldn't change the coding). So, my real & initial(=FULL) dataset contains 161 rows (=records='studies') grouped into 71 'populations', but my the purpose of my "Main" macro is to repeatedly pull subsamples from that full dataset - each of those subsamples is then made up of a header row plus 1 row PER population (=72 total - that's why I was referring to 71rows AND 71populations - b/c, apart from headers, #rows=#pops PER subsample).
So "Main" copies the 161rows x 21cols full dataset directly into OrigDataArr() (note: the file I attached earlier only contained 11cols, not all 21 - again, just to simplify life). Then -and this is one of the parts we've been dealing with- it cuts that array down into TempDataArr(), which only includes the columns selected by the user via listbox (so 161rows x ??cols). Then "Main" runs its subsampling procedures, copying each subsample below the last in SubsamplesDataArr(). So, if "Main" were told to pull 6 subsamples of 8cols each, the finished SubsamplesDataArr() would contain 72*6rows x 8cols... plus an extra column to identify which subsample each row belongs to (a hassle but it's necessary for the next step) - therefore: 72*6r x 8+1c = 432r x 9c. After that point, I'm now working on some code that reformats the array from a tall, skinny shape (e.g. 432 rows!) to the short, wide shape (e.g. 72rows x 6*9cols - so the subsamples are stacked horizontally, not vertically) & then copies THAT last array into a csv file so that it's useable by my statistics program... But that part is going ok, so I won't explain any more of the logic or details of it here. I only need you to help me with the parts I've described above in blue.

I hope that made things clearer not more confusing for you... it's so hard for me to tell b/c I've been immersed in this junk for SOOOO long that it's all just 2nd nature to me.

TempDataArray is dimensioned like this:
ReDim TempDataArr(TotalRows, colCount) As Variant)
but its' dimensions are different than the SubsampledDataArr and then an attempt is made to try to feed data to the SubsampledDataArr:
SubsampledDataArr(ss, p, c) = TempDataArr(p, RandNumbChosen, c) where you changed the dimensions of the TempDataArray without redimensioning and filling it with data it beforehand.

Right, well that's precisely where & why I'm stuck. My original "Main" macro was written solely with an 'eye' to the subsampling procedure & whether or not it was doing that correctly. But it wasn't really built in a way that makes any other modifications or improvements particularly easy. I'd rather not fiddle with the 'core' of the "Main" macro itself since that took so very very long to get right but, at the same time, I also don't know how to convert the arrays etc from there so they'll be useable for other parts of the code...
Now as for feeding TempDataArr() into SubsampledDataArr(), that code (or, more accurately, a very slightly different version of it - using colCount=a constant instead of colCount=listbox picks) does actually work correctly, so I don't know what to tell you about that... What would help clarify it? A copy of that working code too??

As for

'iRow' is never valued prior to its' use

I actually don't think that any of that section is necessary any more since I'm planning on saving the SubsampledDataArr() directly into csv format & don't need to have it copied into a worksheet too. The reason why I'm doing that is b/c excel's 65,536 row limit will only allow me to put ~900 subsamples on a sheet whereas I'll always need at least 10,000 if not more. Similarly, while my stats program requires that the different subsamples each have their own column (as in the HorizArr), excel's 256 column limit would only allow for 10-30 subsamples per sheet in that format. And before you ask, I've decided not to skip the vertical array step since, if I really wanted to double-check the data before saving to csv, I could always have the TempDataArr copied into a visible worksheet range for easy reading/scanning.
So to make life simpler for you, why don't you just remove all the coding from "iReachBottom" to "Next p" and then substitute something like "ResultsSheet.Sheets(3).Range(Cells(1, 1), Cells(x, y)).Value = SubsampledDataArr" (substituting the appropriate variables in lieu of x & y) so that you can see the results immediately, without all the hassle of saving to csv etc.. So long as you don't enter a huge number into textbox1 (= iNumSubsamples), this should work just fine...

Ahh, this is getting SO complicated.
Well, thank you, thank you, thank you!!! I hope you know how grateful I am for all of your help.

ps: do you want me to attach a revised version of the file that includes the csv stuff I'm working on now, or are you happy to keep things as simple & clear-cut as possible?

Hi again!

While I was waiting for your response, I posted to the other thread I referred to in my last post here:

http://www.excelforum.com/showthread.php?t=321263

I'll need to review your comments (and hopefully digest your requirements!) before I get going on this. If you would attach your latest version of the file, it would definitely help me out.

If the need arises, I will send you a private message on how to contact me.

I'll keep an eye out for the file...

theDude

Fern,

I went ahead with coding even though I didn't get your latest file...I hope I have the solution for you

The code will load an array w/data based on the number of samples requested (from TextBox1) and then generate "random" samples (using the Rnd function) and the array contains the same number of columns (plus 1 for the subsample ID); it then dumps the array to a new worksheet in the workbook:

Please Login or Register  to view this content.

Test drive it and let me know if it meets your needs...

theDude

Hi theDude,

I've spent the last few days studying your suggested code, adding & changing bits so it fits with what I already have, debugging & reformatting, and crossing my fingers that the macro will finally work.

And so I think I've finally got about 98% of this code finished, with only one fundamental/important error (plus some formatting issues) still refusing to resolve itself. I'm attaching a newer copy of my dataset & code, along with some examples of the macro outputs resulting from the code you posted, the code in this attached file, and my ideal output/results.

In case anyone else is reading this thread & doesn't want to download the whole file but might have some brilliant ideas about the remaining 'core' problem, here it is in a nutshell:
I'm having trouble getting the macro to correctly interpret the single number picked via the variable "RandNumbChosen". RandNumbChosen calls a function that then refers to a cell in a separate sheet. That cell contains a random number between 1 & the max# of rows/studies in a specific population --> meaning that 1 random study is picked from EACH population for each subsample).
At this point, the RandNumbChosen is being correctly determined but I don't know how to interpret that number so it refers back to a specific ROW in the SourceSheet, one which is dependent on the subsample & population in question...

For example: If this is my SourceSheet...
RecordNumber__PopCount__StudyCount__Population__Study__StudiesInPop__Group
1_______________1__________1___________1_______12.5______4________25.02752085
2_______________1__________2___________1_______14_______4________25.25330077
3_______________1__________3___________1_______15_______4________54.11986696
4_______________1__________4___________1_______16_______4________8
5_______________2__________1___________2_______17_______2________13.56195782
6_______________2__________2___________2_______18_______2________9.616652224
7_______________3__________3___________3_______18.3______1________54.11986696
8_______________4__________4___________4_______18.5______3________16.93280839
9_______________4__________5___________4_______19_______3________24.39979508
10______________4__________6___________4_______20_______3________6.66

Then if RandNumbChosen=3 for Pop#1 (ie for p=1), that means that the function has decided to pull/select the 3rd row from THAT population (not from the entire sheet!!). I'd then want the macro to use that information to identify the single row that had the value of p (=1) in its "PopCount" column AND the value of RandNumbChosen (=3) in its "StudyCount" column... But, as far as I can tell, that'd mean I'd either have to use some crazy combination of an 'Intersect' command, a fancy 'And' command, a ton of nested loops, &/or a VLOOKUP-type function that calls on TWO lookup_values instead of just one (doesn't exist??).
I've been playing with possible ways of doing this for more than a day but can't seem to get anything to work. Does anyone understand my dilemma &/or know of a solution?? Alternatively perhaps it'd be easier if you just looked at the whole code/file...

Finally, theDude, I thought I should explain why your randomization method won't work for me & why I've worked so hard to integrate it into the complex coding I've already written... since I don't want you to waste your time changing it back to the standard excel/vba format when I'm only going to have to switch it back once you're done In simple terms, my version is just an attempt to avoid the limitations & imperfections of the built-in Rnd & Randomize functions. My MTChoice() function (which is called by the RandNumbChosen variable mentioned above) gives a single random integer, just like your TempDataArr(Int(((TotalRows - 1) * Rnd) + 1), j)does - only it's more statistically accurate. You can see my whole debate & struggle with this topic in another forum by reading the thread at: www.xtremevbtalk.com/showthread.php?t=201215 (note: some of the commands & coding details mentioned there aren't in my script anymore... but the basic logic remains)...

One last clarification: the attached file doesn't contain ALL of the columns & populations from my original dataset - I've had to delete rows & columns so that the file is small enough to attach to the forum. I'm also going to move the 3 outputs to a secondary workbook and attach that in a separate thread. Hope that's ok. If you want to see the full dataset, let me know & I'll email it to you. However, it shouldn't really matter that the example dataset is smaller - the coding & formatting patterns should still hold, no matter how many cells are being used...

Sigh, well I hope this will help you understand where I've been, where I am & where I'm still struggling to go. Thankfully this code is nearly nearly nearly finished (which much credit going to theDude for his ongoing help). Hopefully you &/or can solve these last few knots without too much trouble.

THANK YOU SO MUCH!

Hi Fern!

You finally came up for some air! You've been immersed in your project for some time since your last post. I was beginning to wonder if you had finished it and were so spent that you went to some tropical locale for some 'R & R'; but apparently not... Too bad, you deserve it!

Regarding the Randomize/Rnd code I provided, I knew you had the MTChoice function; I just used it as a placeholder for you to insert yours in place of it...

Anyway, I'll take a gander at the latest file and your detailed explanation of this final issue and hopefully come up with a solution for the last piece of your 'puzzle'; check back in a day or two.

theDude

Fern

It took less time than I thought...

Here's a function to copy to Module1 in your file:

Please Login or Register  to view this content.

Copy this macro to Module1 to test the function (it's hard-coded against worksheet W1 and prompts you enter simulated values for 'p' and 'RandNumbChosen') and then displays a dialog with the correct row found:

Please Login or Register  to view this content.

If the function works as you want, then find in your main code:

Please Login or Register  to view this content.

And replace it with this:

Please Login or Register  to view this content.

Hopefully this will let you put your project to bed soon...

theDude

Thank you theDude! I'm really grateful for all of your help. Sorry for the delay but I've had a busy couple of days (meetings & appointments) and so didn't have an opportunity to write back, even tho I was actually working on the code whenever I had a free moment (on the bus, waiting to be seen, etc).

As for the code you posted... I'm sorry to say that it doesn't work - regardless of whether I use the testFindRow() sub or modify the main macro like you suggested. It keeps hitting an error ("Application-defined or object-defined" etc) at the "Set PopRange" command of FindRow. It's not the range reference itself b/c the values of its end points/cells & userSheet are correct. And PopRange is defined (correctly, I think) as a range. So I'm a little confused why the code would work for you but not for me - I'm trying to run it in exactly the same file I posted yesterday, which is the one I'm assuming you wrote it for. It worked for you, right?
What's really wierd is that I actually got it to run ONCE but haven't been able to get it to work since then. Very confusing. It's a great solution though, so I'll try to fiddle with it to see if I can get it to work via some other, less elegant, way. As always, any ideas or updates would be appreciated.

I'd also be curious to hear if you had any suggestions for how to fix the formatting flaws or even if you could identify exactly where I'm going wrong. I figured out where ONE of the errors lies: the duplication of ss2 comes about b/c I've still not perfected my definition of ssRows; however I'm still struggling with HOW to fix it now that I know where to look:

Please Login or Register  to view this content.

As for the blank but not skipped columns, the only solution I've come up with so far is to revert to loading all the original dataset into an initial array and then to move only the selected columns over to a secondary array...sort of like this:

Please Login or Register  to view this content.

It isn't perfect, and it adds extra layers of looping, but I think it might work - which at this point is far far more important than elegance or even speed..

Anyway, I need to go make some lunch & then I'll return to the task of wrapping my feeble brain around this frustrating code. Thanks again, F

Fern,

Sorry it doesn't work for you! I wrote it and tested it with the test macro only in the latest file you posted...I've re-run the test macro many times and can't get it to reproduce the error you get. I did notice in the test macro code I posted that when it went up to the web, one line suddenly had a few extra spaces (at the end between 'Rows.' & 'Count') that weren't in the original:

TotalRows = ThisWorkbook.Worksheets(UserSheet).UsedRange.Rows. Count

Maybe there's something else like that when you copied the code down from the thread?

I'll take a look at your other question and get back soon...

theDude

No worries.

So, you've made a good point about using a selected (not fixed) column reference in my definition of RelativeRow, but I don't think it'll actually be a problem b/c I'll ALWAYS be selecting/requesting the first six columns (for identification & cross-referencing purposes), regardless of which UserSheet or iNumSubsamples is used - so as long as the reference isn't to a column to the right of that, I think it should be ok. Yes, I could have written the macro so they'd be automatically loaded into the SubsamplingDataArr etc, but that's just one more layer of fussy coding & potential errors to deal with - and I don't mind always selecting those extra columns...

However something still isn't working there, so I'm going to step through the results of each line to see where it's going wrong. My suspicion is that at least part of the prob is caused by the overlapping loops of ss & VRow; for example (with 71pops+1header per ss), the VRow loop now runs all the way through rows#1-144 and then gets repeated for any additional subsamples.... rather than doing #1-72 for ss1 & then #73-144 for ss2. Understand? Anyways, if I can eliminate one of those layers (maybe VRow?), then perhaps I'll simply add a loop for p which'd eliminate the need to calc RelativeRow... I didn't do it earlier b/c I didn't want the code to loop through VRow, VCol, ss, AND p over & over - but perhaps this'll solve everything... cross your fingers.

I'll check back in again later this evening - hopefully with a stunning victory to report. Cheers, F

Ok, solved the HorizArr problem by using the following code:

Please Login or Register  to view this content.

Now I've just got to figure out WHY the results seem to be randomly missing rows (& how to fix it). Plus I still haven't managed to eliminate the supposedly skipped columns from the results either - though the headers are finally lined up properly AND I'm no longer missing the "RecordNumber" column.

Nearly there...

Whoo hoo!
Another giant leap forward: I've solved the missing ROW problem!!

Now I'm trying to figure out how to keep the unselected COLUMNS out of the SubsamplingDataArr() since they're still being transferred over (they're just blank)... I'd rather not have to resort to setting up an extra array if I don't have to (see idea in prev post), since I'm worried that I'd only be inviting tragedy by messing with the near perfect code I've got now. The only other idea I've come up with, for better or worse, is to insert a line of code that DELETES blank columns from TempDataArr before the subsampling procedures kick in or SubsamplingDataArr is produced. It'd involve a momentary waste of memory & time plus some fussing to re-count the number of columns in the array, but it might end up being easier than any other option I come up with.

I'd love to hear your feedback about these 2 possible routes &/or if you can suggest any better alternatives - since even just getting some help picking WHICH direction to follow would be really useful at this point.

Thrilled to be just one tiny step from finishing, F

Fern,

I tried running your code (my first time, actually) from your last post but can't get it to work. I was able to detect that the TempDataArray was getting populated w/blank columns, but it appeared to be dependent on which columns were selected from the form. Unfortunately, I was only able to confirm it once, then I kept getting an error when trying to populate the TempDataArray.

I reran my original code to populate a Temp array and it worked fine under many test cases (various combinations of selection options ror columns to load), but I wrote it based on Option Base 0 as opposed to your Option Base 1. There may be something worth investigating there, but on a cursory review (I have a very busy schedule through Friday), it didn't appear so...

So, hopefully you will find a solution before I can (and I think you will!), but just in case, please post your latest version of your code (and sample output, last time you said you would, but you must have forgot to zip them up in the attachment). If I get a little time over the next few days and you haven't solved it, maybe I can help...

theDude

Hey there,

Sorry to hear you're so busy. I hope things ease off for you soon.

I was disappointed to hear that you couldn't diagnose my error straight away (altho the "option base 0 vs 1" idea sounds promising). I guess I'll have to do some more brainstorming and trial-&-error programming on my own. But just in case you feel the urge to tinker during your coffee breaks here's a copy of my example data file (+working macro) and an output csv, as requested.
As you'll see in the results output, and as you mentioned in your post, there's no obvious pattern to which columns are skipped and which are left blank. I had thought that NONE of the unselected columns were being skipped, but that doesn't seem to be the case; some are & others aren't. Very odd.

I'll try to pick it apart piece by piece tomorrow, and will let you know how I'm progressing over the next two or three days (since you'll be so busy in the meantime anyhow).

Thanks again for all of your help, F

thedude,

Before getting into VBA, did you ever get into other forms of programming?

Sorry it took a while to respond...

To answer your question, I have dabbled in many programming languages, became proficient in a few. One of my favorites is AppleScript...

Now I spend almost all of my programming effort in Visual Basic.

Keep on coding!
theDude