Solved: Unique values from date range

[tqm1]

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]

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]

error in code giving message
compile error:
next without for

[mikerickson]

Oops, I missed an End If.

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

[anandbohra]

the compile error u had rectified but it is doing nothing no such data is added to combobox1

[Digita]

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]

Dear Sir,

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

Please see attached sheet.

[anandbohra]

thanks tqm1

this one is working fine

[tqm1]

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]

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? & why it is not running there

[mikerickson]

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

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

[tqm1]

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]

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

[VBA]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
[/VBA]


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