Hi all, basically I've got 3 bits of code that i'm trying to stitch together and am only 1/2 way through my VBA for Dummies book...
What i'm trying to do is create an input form that appears with 2 options. Based on the option selected, one of 2 ranges will be identified and passed through to a function procedure as the input array.... after the function runs, I would like it's results (another array) to be passed through as the elements or variables(?) for another array in the sub procedure...
I have never used input forms before so i'm kind of lost right off the bat and i'm only really just starting to understand what my macro recorder's been doing for me all these years!
Currently I have a macro that creates what you see in the attached sample workbook... I would at this point run the code in Step 3 below, but I would like to modify by adding Setp 1 and 2 below at this point...
Step 1- User input... I have no idea how this is supposed to go, but I would like "OptionModelType" to select Range(=LEFT(B30:End(xlToRight),3) ie. In the last part of code below (Step 3) the code enters the first 3 characters of cell30 into cell1 for each column... if I do this first, then the desired range would be B1:B last column, obviously if the user input box is going to work here I will have to move that process to have already occurred before this point unless the range can be determined without this step at all? OptionModelName is basically the same range except for the full value in row 30 and not just the first 3 characters.
once the user has made a selection and clicked "Go", I would like the selected range to be used as the input array in this function... By the way, this function has an optional count operation that I don't need, i'm just not 100% sure which code an can delete without messing it up, so I would like to have Count set to False automatically all the time.
Step 2- The UniqueItems Function
Finally this function produces a list of all the unique values in the input array, but rather than having them copied into cells, (unless that's a necessary step...) I would like this list of values to be passed through as the array elements for VArray in this next bit... ie. VArray = Array("results from this function" instead of the hard keyed variables in the code below)
Step 3- The rest... FYI I also don't want to delete any worksheets, but for some reason the code doesn't seem to delete them anyway... again, have been hesitant to remove any lines as the code still functions as is
I have attached a sample worksheet and any help would be very much appreciated! Also, in the original workbook, many of the values have formulas and vlookups, but in order to keep this small and simple I've just included values and formats in this sample...sample3.xlsm
Thanks,
Joe
Bookmarks