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

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,

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

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,

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

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

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