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

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

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

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