Try again - adress from a range to combobox
 

Hi

if u have arange maybe
$B$31:$B$43
And i have the values from this range in a combobox
how do i get det address from the range when i select a value in my
combo box.
Let us say that $B$31:$B$43 is numbers and in $B$40 are there maby number 8
i select number 8 in my combobox and the I want to use the address where
number 8 are, in this case $B$40

Hope someone can help

Alvin

Try again - adress from a range to combobox
 

Hi again,
A simple Userform with a combobox and textbox. The combobox
is filled with data from named range "MyRange": selection form the combobox
puts the address of the element of "MyRange" in the textbox.

HTH

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rng As Range
Set rng = Range("Myrange")
n = CInt(ComboBox1.Value)
TextBox1.Value = rng(n).Address
End Sub


Private Sub UserForm_Initialize()
' "MyRange" is , for example, "A11:A20" with values 1 to 10
ComboBox1.RowSource = "MyRange"
End Sub

"alvin Kuiper" wrote:

Hi

if u have arange maybe
$B$31:$B$43
And i have the values from this range in a combobox
how do i get det address from the range when i select a value in my
combo box.
Let us say that $B$31:$B$43 is numbers and in $B$40 are there maby number 8
i select number 8 in my combobox and the I want to use the address where
number 8 are, in this case $B$40

Hope someone can help

Alvin

Try again - adress from a range to combobox
 

Private Sub Combobox1_Click()
msgbox "B" & 31 + Userform.Combobox1.ListIndex
End Sub

--
Regards,
Tom Ogilvy


"alvin Kuiper" wrote in message
...
Hi

if u have arange maybe
$B$31:$B$43
And i have the values from this range in a combobox
how do i get det address from the range when i select a value in my
combo box.
Let us say that $B$31:$B$43 is numbers and in $B$40 are there maby number
8
i select number 8 in my combobox and the I want to use the address where
number 8 are, in this case $B$40

Hope someone can help

Alvin

Try again - adress from a range to combobox
 

Thanks Toppers
you are the best
many many thanks

Alvin


"Toppers" wrote:

Hi again,
A simple Userform with a combobox and textbox. The combobox
is filled with data from named range "MyRange": selection form the combobox
puts the address of the element of "MyRange" in the textbox.

HTH

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rng As Range
Set rng = Range("Myrange")
n = CInt(ComboBox1.Value)
TextBox1.Value = rng(n).Address
End Sub


Private Sub UserForm_Initialize()
' "MyRange" is , for example, "A11:A20" with values 1 to 10
ComboBox1.RowSource = "MyRange"
End Sub

"alvin Kuiper" wrote:

Hi

if u have arange maybe
$B$31:$B$43
And i have the values from this range in a combobox
how do i get det address from the range when i select a value in my
combo box.
Let us say that $B$31:$B$43 is numbers and in $B$40 are there maby number 8
i select number 8 in my combobox and the I want to use the address where
number 8 are, in this case $B$40

Hope someone can help

Alvin