PDA

View Full Version : Solved: Unique values from date range



tqm1
06-26-2007, 09:03 PM
Dear Sir,

I have following two columns as

--Date------Code
02-06-07----10
02-06-07----20
03-06-07----30
03-06-07----30
03-06-07----40
03-06-07----50
04-06-07----50
04-06-07----60
05-06-07----70
06-06-07----80
06-06-07----90

I wnat to fill Combobox1 with unique codes between Date 03-06-07 to 04-06-07

The unique values are between date range are: 30,40,50,60

Please help

mikerickson
06-26-2007, 09:39 PM
If your dates are in column A and codes in column B, this should do it.

Sub fillUnique()

Dim dateRay As Range
Dim xVal As Variant
Dim myColl As New Collection

With ThisWorkbook.Worksheets("sheet1")
Set dateRay = Range(.Range("a1"), .Range("a65536").End(xlUp))
End With

On Error Resume Next
For Each xVal In dateRay
If DateSerial(2007, 3, 6) < xVal.Value _
And xVal.Value <= DateSerial(2007, 4, 6) Then
myColl.Add Item:=xVal.Offset(0, 1), key:=CStr(xVal.Offset(0, 1))
End If: Rem debug *********
Next xVal
On Error GoTo 0

For Each xVal In myColl
ComboBox1.AddItem xVal
Next xVal

End Sub

anandbohra
06-26-2007, 10:28 PM
error in code giving message
compile error:
next without for

mikerickson
06-26-2007, 10:31 PM
Oops, I missed an End If.

I edited in a debugging line above to correct the error.

anandbohra
06-26-2007, 10:55 PM
the compile error u had rectified but it is doing nothing no such data is added to combobox1

Digita
06-27-2007, 12:07 AM
Mikerickson,

Your code needs another amendment. The day and month number in the Dateserial function is put in the wrong order. The valid format is dateserial(year, month, day).

Apart from this, your code is working perfectly.

Regards


KP

tqm1
06-27-2007, 03:33 AM
Dear Sir,

I have made some changes in your codes but it still reply some strange result.

Please see attached sheet.

anandbohra
06-27-2007, 04:39 AM
thanks tqm1

this one is working fine

tqm1
06-27-2007, 04:59 AM
But it does not work with me.

May this is place difference?

I am in Pakistan and you are outside Pakistan.

Please attach worksheet with modifications.

anandbohra
06-27-2007, 05:03 AM
i am talking about the same zip file u had attached here it is adding the unique records for the given date range in Combobox

no idea about the place where u r?:dunno & why it is not running there:banghead:

mikerickson
06-27-2007, 05:50 AM
Mikerickson,

Your code needs another amendment. The day and month number in the Dateserial function is put in the wrong order. The valid format is dateserial(year, month, day).

Apart from this, your code is working perfectly.

Regards


KPI thought that 4/6/07 in the OP was refering to April 6. Glad it worked out.

tqm1
06-27-2007, 06:31 AM
It is very amazing for me that my attached sheet is working there.

By the way what Date format you are using in Control Panel's Regional and Languages Options.

I am using dd/MM/yyyy

May this is problem?

anandbohra
06-27-2007, 10:07 PM
Hi TQM1
I found the problem in your code
if i am not mistaken it is giving u 10,20,30,40,50,60 right
here is simple amendment in your code

Sub Oval1_Click()


Dim dateRay As Range
Dim xVal As Variant
Dim myColl As New Collection

With ThisWorkbook.Worksheets("sheet1")
Set dateRay = Range(.Range("a1"), .Range("a65536").End(xlUp))
End With

On Error Resume Next
For Each xVal In dateRay
'If DateSerial(Range("E1")) < xVal.Value _
' And xVal.Value <= DateSerial(Range("E2")) Then
' myColl.Add Item:=xVal.Offset(0, 1), key:=CStr(xVal.Offset(0, 1))
'End If


If DateValue(xVal.Value) >= DateValue(Range("E1")) _
And DateValue(xVal.Value) <= DateValue(Range("E2")) Then
myColl.Add Item:=xVal.Offset(0, 1), key:=CStr(xVal.Offset(0, 1))
End If
Next xVal
On Error GoTo 0


Sheets("sheet1").ComboBox1.Clear

For Each xVal In myColl
Sheets("sheet1").ComboBox1.AddItem xVal
Next xVal

'Sheets("sheet1").ComboBox1.DropDown

End Sub



the bold sentence contain logical mistake as u r comparing E1 with Xval instead of Xval with E1