Computer Hangs

[tqm1]

Dear Expets

When I run following procedure, then computer hangs then I have to press ALT+CTRL+DEL or Power off.
What is wrong? Please help
[vba]
Sub Findunique()
Dim dateRay As Range
Dim xVal As Variant
Dim myColl As New Collection
Dim FDATE As Date
Dim LDATE As Date

FDATE = Sheets("Rpt_Date").TextBox1.Value '01-07-06
LDATE = Sheets("Rpt_Date").TextBox2.Value '15-06-07

With Worksheets("weights")
Set dateRay = .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
End With

On Error Resume Next
For Each xVal In dateRay
Range("B3").Value = xVal
If DateValue(xVal.Value) >= DateValue(FDATE) _
And DateValue(xVal.Value) <= DateValue(LDATE) Then
myColl.Add Item:=xVal.Offset(0, 1), key:=CStr(xVal.Offset(0, 1))
End If
Next xVal
On Error GoTo 0

Sheets("Rpt_date").ComboBox1.CLEAR

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

End Sub
[/vba]

[mdmackillop]

Can you post your workbook?

[tqm1]

I have overcome the problem
The following line of code was reason to hangs up
[vba]
Range("B3").Value = xVal
[/vba]

But code takes more than 10 seconds to display results
Is it possible to SPEED UP codes?
Please modify

[mdmackillop]

Why not filter unique items to another location and use that range to fill your combobox?

[tqm1]

Dear Sir

Where to Add autofilter unique:=true in the following procedrue

[vba]
rng.AutoFilter Field:=4, Criteria1:="<>" ' I want to filter all unieque records from Field4
rng.AutoFilter Field:=12, Criteria2:=(Sheets("Rpt_date").TextBox1.Value), _
Operator:=xlOr, Criteria2:=(Sheets("Rpt_date").TextBox2.Value)
[/vba]

[mdmackillop]

Use Advanced Filter, not Autofilter

[vba]Sub Macro1()
Range("A1:A26").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"K1"), Unique:=True
ActiveSheet.ComboBox1.List() = Range(Range("K2"), Range("K2").End(xlDown)).Value
Range(Range("K1"), Range("K1").End(xlDown)).ClearContents
End Sub
[/vba]

[tqm1]

But how to adjust following Date range criteria in your Advance Filter
[vba]
rng.AutoFilter Field:=12, Criteria2:=(Sheets("Rpt_date").TextBox1.Value), _
Operator:=xlOr, Criteria2:=(Sheets("Rpt_date").TextBox2.Value)
[/vba]

[mdmackillop]

As I requested in Post #2, can you post your workbook.

[tqm1]

Dear Sir,
The attachment is workbook
Please Review sheet2

[Norie]

Eh, there isn't a Sheet2 in your attachment.

Do you mean the sheet called Rpt_Dare?

[mdmackillop]

[VBA]Option Explicit
Sub Macro1()
Dim rng As Range, cel As Range, Dte1 As Date, Dte2 As Date

With Sheets("Weights")
Range(.Cells(1, 12), .Cells(1, 12).End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=.Cells(2, 12), CopyToRange:=.Cells(1, 26), Unique:=True
Set rng = Range(.Cells(2, 26), .Cells(2, 26).End(xlDown))
rng.Sort rng(1), xlAscending
Dte1 = CDate(Sheets("Rpt_Date").TextBox1.Value)
Dte2 = CDate(Sheets("Rpt_Date").TextBox2.Value)
For Each cel In rng
If cel >= Dte1 And cel <= Dte2 Then
Sheets("Rpt_Date").ComboBox1.AddItem cel
End If
Next
Range(.Cells(1, 26), .Cells(1, 26).End(xlDown)).ClearContents
End With
End Sub
[/VBA]

[tqm1]

Run-Time Error '1004'
Method 'Range' of object worksheet' failed.
and debug shows error in following two lines
[vba]
Range(.Cells(1, 12), .Cells(1, 12).End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=.Cells(2, 12), CopyToRange:=.Cells(1, 26), Unique:=True
[/vba]
Please modify

[mdmackillop]

Try recording a macro to carry out the operation and compare the codes.

[tqm1]

But I do not know how to adjust more than one criteria in Advance Filter.
Please help at this final touch.

[mdmackillop]

This works for me. What version of Excel are you using?

[rbrhodes]

Hi tqm1,

If you must Xpost _please_ provide a link to the other post at least.

http://www.ozgrid.com/forum/showthread.php?t=72302

Here's my version of your sheet with mdmackillop's code slightly revised for the problems I was having testing in Excel '97. Figured that would provide as many problems as possible!

Two main problems:

Filtering with copy was not happy because it wasn't from the activesheet.

Min Date was fine but Max Date was returning invalid date with format as dd-mm-yy. Date in textbox 21-7-06 returned as 6-7-21 . Changed format to mm-dd-yy and it works fine.

I reactivated the Sheet Activate sub so it now fills the textboxes with default dates.

This is now a working version. Type a date in the textboxes and you're good to go. Blank or invalid date you get a message.

Cheers,

dr

[tqm1]

Dear Sir,

In combobox1, your codes displays unique Dates from column L.

But as I mentioned before that I want to show unique values from Column "D" against date range given in textbox1 and texbox2.

So I changed cell referece to column 4
[vba]
'Advanced filter
Range(Cells(1, 4), Cells(1, 4).End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=Cells(2, 12), CopyToRa
[/vba]

Now nothing is appear in combobox.

this is column D
vno9098/LI1.C.CART1.C.CART1.C.CART

Please help again

[Aussiebear]

It seems that tqm1, pays no attention to the requests to link any cross posts. It is not the first time that tqm1 has cross posted and in my humble opinion, tqm1 has been mining code from more than one site.

I would suggest that tqm1 needs to spend some time in isolation.

[johnske]

Hi tqm1,

If you must Xpost _please_ provide a link to the other post at least.

http://www.ozgrid.com/forum/showthread.php?t=72302 ...

tqm1,

You have been banned (again) from posting on the VBAX forums. The reason being: For cross-posting.

You have been warned many times about this and have previousy had a 48 hour ban imposed by me yet you still persist.

This time the ban will apply for 96 hours, should you wish to contest this, please email me or any other Administrator. The period I have banned you for may then be reviewed as to whether I may have been too harsh (or too lenient).

This free time is to allow you to review your situation. Please be warned that you cannot continue wasting helpers time by cross-posting - it would be far easier to place a permanent ban on you posting on VBAX.

Have a nice day,
John