ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   select range from a value (https://www.excelbanter.com/excel-programming/353083-select-range-value.html)

alvin Kuiper
select range from a value
 
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i get the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to see the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

Gary''s Student
select range from a value
 
Here is some code that you can adapt for your combobox.

It looks down column A for a certain date and builds a range for the cells
that contain that certain date. Once the range has been built, it is output
in your desired format.



Sub findrange()
Dim s As String
Dim ss As String
Dim r As Range
ss = "12/12/2006"
For i = 1 To 100
s = Cells(i, 1).Value
If s = ss Then
If r Is Nothing Then
Set r = Cells(i, 1)
Else
Set r = Union(r, Cells(i, 1))
End If
End If
Next
MsgBox (r.Address)
End Sub
--
Gary''s Student


"alvin Kuiper" wrote:

Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i get the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to see the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

Bob Phillips[_6_]
select range from a value
 
Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Hi garry
Get an error in
MsgBox (r.Address)

Don't know why
Alvin


"Gary''s Student" wrote:

Here is some code that you can adapt for your combobox.

It looks down column A for a certain date and builds a range for the cells
that contain that certain date. Once the range has been built, it is output
in your desired format.



Sub findrange()
Dim s As String
Dim ss As String
Dim r As Range
ss = "12/12/2006"
For i = 1 To 100
s = Cells(i, 1).Value
If s = ss Then
If r Is Nothing Then
Set r = Cells(i, 1)
Else
Set r = Union(r, Cells(i, 1))
End If
End If
Next
MsgBox (r.Address)
End Sub
--
Gary''s Student


"alvin Kuiper" wrote:

Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i get the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to see the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

Bob Phillips[_6_]
select range from a value
 
Alvin,

I just renamed my combo to your name, but it still works fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25 then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

Bob Phillips[_6_]
select range from a value
 
No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Ok
Can i get it to work in a userform?

Alvin

"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Hi bob
I done it
Thanks for your help

Alvin


"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Just a little more help
Its working
but can i change it so it dosn't take the first row
I will have this to a head line

Alvin


"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10 times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

Bob Phillips[_6_]
select range from a value
 
Just start the loop from row 2 not 1


For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Just a little more help
Its working
but can i change it so it dosn't take the first row
I will have this to a head line

Alvin


"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for
me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10
times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i
want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to
A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Hi bob
why i ask because i have try that
and get error in
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address

Alvin


"Bob Phillips" wrote:

Just start the loop from row 2 not 1


For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Just a little more help
Its working
but can i change it so it dosn't take the first row
I will have this to a head line

Alvin


"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine for
me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe 10
times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox i
want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20 to
A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

Bob Phillips[_6_]
select range from a value
 
It also needs to test for starting at row 2 later

Private Sub combo_sedatoer_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 2 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & _
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
why i ask because i have try that
and get error in
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address

Alvin


"Bob Phillips" wrote:

Just start the loop from row 2 not 1


For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Just a little more help
Its working
but can i change it so it dosn't take the first row
I will have this to a head line

Alvin


"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine
for
me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex ,
1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A"
&
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox
combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart &
":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote
in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe
10
times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox
i
want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20
to
A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin

alvin Kuiper
select range from a value
 
Many many thanks bob
now its working with all i want
Thanks again

Best regards

Alvin


"Bob Phillips" wrote:

It also needs to test for starting at row 2 later

Private Sub combo_sedatoer_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 2 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & _
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Hi bob
why i ask because i have try that
and get error in
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address

Alvin


"Bob Phillips" wrote:

Just start the loop from row 2 not 1


For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in message
...
Just a little more help
Its working
but can i change it so it dosn't take the first row
I will have this to a head line

Alvin


"Bob Phillips" wrote:

No what I gave you was for a worksheet control toolbox combo.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi bob

The combobox is it in a Userform or?

Alvin


"Bob Phillips" wrote:

Alvin,

I just renamed my combo to your name, but it still works fine
for
me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote in
message
...
Hi bob
I try this
Private Sub combo_sedatoer_Click()
MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex ,
1)

End Sub

Private Sub combo_sedatoer_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A"
&
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
.combo_sedatoer.List = Application.Transpose(ary)
End With

End Sub

but when i use my dropdown i get a error in

MsgBox Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex , 1)
don't know why
Alvin



"Bob Phillips" wrote:

Alvin,

here is some code that will work with a control toolbox
combobox

Private Sub ComboBox1_Click()
MsgBox Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary

dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Me
.ComboBox1.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value < dtePrev Then
If i < 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart &
":A" &
iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = Me.Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" &
iEnd).Address
.ComboBox1.List = Application.Transpose(ary)
End With

End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"alvin Kuiper" wrote
in
message
...
Hi
If i in collumn a have dates This i can
order by dates so far so god
A date can bee the same several times
Now I whant to have a Combobox With The dates in
from cllumn A I can that, but if the date are there maybe
10
times
i
get
the
dates in my combobox 10 times
I only what to see it One time ( In SQL i use Disinct)

When i have this date and choose the date in the combobox
i
want
to
see
the
range the value are in
Like if i choose "12-12-2006" and have this value in A20
to
A25
then i
want
to see this "$a$20:$a:$25"

Hoipe some one can help me with this.

Regards

Alvin


All times are GMT +1. The time now is 07:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com