Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > select range from a value
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,058
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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







Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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








Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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










Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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











Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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











Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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











Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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













Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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














Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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
















Reply With QuoteReply With Quote
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 175
Default 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

















Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can change range to select active rows instead of :=Range("S10 ldiaz Excel Discussion (Misc queries) 7 August 29th 08 03:52 PM
When entering data into a range of cells, select the entire range. Q Excel Discussion (Misc queries) 0 September 26th 07 04:36 AM
select range and put range address in variable [email protected] Excel Programming 2 January 25th 06 01:28 AM
Compare a selected Range with a Named range and select cells that do not exist PCLIVE Excel Programming 1 October 18th 05 07:09 PM
Select Sheet then Select Range Gee[_2_] Excel Programming 3 May 27th 04 10:10 PM


All times are GMT +1. The time now is 06:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement