Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a data range using a variable
Hi there... I'm having a little trouble as I have never programmed excel but have studied programming so I know what i'm doing but have no idea how to go about it sometimes :P I have not been able to track down a tutorial for my latest problem... What I have so far is a combo box to select an employee name and a button to sort the database so I only have rows containing that employee name. So in essence I am at the moment left with a set of rows looking like for example: 1 56 57 58 59 1000 and I want to populate a list box with the contents of column E. ie E56-E59 but of course it is not always 56-59 it could be 10-13. Can I approach this along these lines? ListBox1.RowSource = "'Worksheet1'$E$Variable$E$Variable2" *edit* Forgot my question :P If so what syntax do i use with the variables so it knows to look for $E$56 instead of $E$Variable. *edit* Or should I go about it completely differently? :) Thoughs and advice appreciated. D -- Alariel ------------------------------------------------------------------------ Alariel's Profile: http://www.excelforum.com/member.php...o&userid=37644 View this thread: http://www.excelforum.com/showthread...hreadid=572560 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a data range using a variable
I think you mean
ListBox1.RowSource = "'Worksheet1'$E$" & Variable1 & ":$E$" & Variable2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Alariel" wrote in message ... Hi there... I'm having a little trouble as I have never programmed excel but have studied programming so I know what i'm doing but have no idea how to go about it sometimes :P I have not been able to track down a tutorial for my latest problem... What I have so far is a combo box to select an employee name and a button to sort the database so I only have rows containing that employee name. So in essence I am at the moment left with a set of rows looking like for example: 1 56 57 58 59 1000 and I want to populate a list box with the contents of column E. ie E56-E59 but of course it is not always 56-59 it could be 10-13. Can I approach this along these lines? ListBox1.RowSource = "'Worksheet1'$E$Variable$E$Variable2" *edit* Forgot my question :P If so what syntax do i use with the variables so it knows to look for $E$56 instead of $E$Variable. *edit* Or should I go about it completely differently? :) Thoughs and advice appreciated. D -- Alariel ------------------------------------------------------------------------ Alariel's Profile: http://www.excelforum.com/member.php...o&userid=37644 View this thread: http://www.excelforum.com/showthread...hreadid=572560 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a data range using a variable
That is exactly what i meant :) Another query.. If I know the range say $E$2:$E$10 and this contains one one two two three one four five one The listbox comes up the same is there a way to get it to populate as one two three four five without it removing the duplicates in the spreadsheet (like an advanced filter would.) I would like to say I want the data in said range but only list each one once. The reason I would like to keep the duplicates is that if they click on an item in the list box I would like to be able to calculate the number of instances that occur within the range... My query is not how to do all of that just how to populate the list box with unique items while leaving the spreadsheet intact. *idea* Would creating an array and going through each item one by one to see if its unique and then populating the listbox with the array be they way (or a way) to go? any other ideas would be appreciated. Cheers D -- Alariel ------------------------------------------------------------------------ Alariel's Profile: http://www.excelforum.com/member.php...o&userid=37644 View this thread: http://www.excelforum.com/showthread...hreadid=572560 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a data range using a variable
You could use the technique at John Walkenbach's to get a unique list:
http://j-walk.com/ss/excel/tips/tip47.htm Alariel wrote: That is exactly what i meant :) Another query.. If I know the range say $E$2:$E$10 and this contains one one two two three one four five one The listbox comes up the same is there a way to get it to populate as one two three four five without it removing the duplicates in the spreadsheet (like an advanced filter would.) I would like to say I want the data in said range but only list each one once. The reason I would like to keep the duplicates is that if they click on an item in the list box I would like to be able to calculate the number of instances that occur within the range... My query is not how to do all of that just how to populate the list box with unique items while leaving the spreadsheet intact. *idea* Would creating an array and going through each item one by one to see if its unique and then populating the listbox with the array be they way (or a way) to go? any other ideas would be appreciated. Cheers D -- Alariel ------------------------------------------------------------------------ Alariel's Profile: http://www.excelforum.com/member.php...o&userid=37644 View this thread: http://www.excelforum.com/showthread...hreadid=572560 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting a variable range | Excel Programming | |||
selecting Variable range | Excel Programming | |||
selecting a variable row range | Excel Programming | |||
Selecting a variable Range | Excel Programming | |||
Selecting a Range Using a Variable | Excel Programming |