Thread: 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

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
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> 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
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > 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
> >
> >
>
>
>

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
> >
> >

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" <alvinKuiper@discussions.microsoft.com> wrote in message
news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > 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
> > >
> > >
> >
> >
> >

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > 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
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
message
> > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> message
> > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> message
> > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> message
> > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
news:9A1147B1-329F-40A3-979D-A17FDB430394@microsoft.com...
> 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
message
> > > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> > message
> > > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:9A1147B1-329F-40A3-979D-A17FDB430394@microsoft.com...
> > 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > > news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> message
> > > > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> > > message
> > > > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <alvinKuiper@discussions.microsoft.com> wrote in message
news:95A634D1-5985-475D-9F7D-066D88BF4DEF@microsoft.com...
> 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > news:9A1147B1-329F-40A3-979D-A17FDB430394@microsoft.com...
> > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
message
> > > > news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> > message
> > > > > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > > > > 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" <alvinKuiper@discussions.microsoft.com> wrote
in
> > > > message
> > > > > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <alvinKuiper@discussions.microsoft.com> wrote in message
> news:95A634D1-5985-475D-9F7D-066D88BF4DEF@microsoft.com...
> > 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" <alvinKuiper@discussions.microsoft.com> wrote in message
> > > news:9A1147B1-329F-40A3-979D-A17FDB430394@microsoft.com...
> > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> message
> > > > > news:B22E2F36-0539-4533-924F-939AB79614D4@microsoft.com...
> > > > > > 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" <alvinKuiper@discussions.microsoft.com> wrote in
> > > message
> > > > > > > news:B932F528-39FA-4771-A50D-6465F8DA0F67@microsoft.com...
> > > > > > > > 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" <alvinKuiper@discussions.microsoft.com> wrote
> in
> > > > > message
> > > > > > > > > news:98222798-273A-4F43-9D9C-74D947BAD2DC@microsoft.com...
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>