PDA

View Full Version : Computer Hangs



tqm1
06-29-2007, 08:30 PM
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

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

mdmackillop
06-30-2007, 01:03 AM
Can you post your workbook?

tqm1
06-30-2007, 01:51 AM
I have overcome the problem
The following line of code was reason to hangs up

Range("B3").Value = xVal


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

mdmackillop
06-30-2007, 01:53 AM
Why not filter unique items to another location and use that range to fill your combobox?

tqm1
06-30-2007, 04:19 AM
Dear Sir

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


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)

mdmackillop
06-30-2007, 04:35 AM
Use Advanced Filter, not Autofilter

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

tqm1
06-30-2007, 06:44 AM
But how to adjust following Date range criteria in your Advance Filter

rng.AutoFilter Field:=12, Criteria2:=(Sheets("Rpt_date").TextBox1.Value), _
Operator:=xlOr, Criteria2:=(Sheets("Rpt_date").TextBox2.Value)

mdmackillop
06-30-2007, 10:12 AM
As I requested in Post #2, can you post your workbook.

tqm1
06-30-2007, 07:08 PM
Dear Sir,
The attachment is workbook
Please Review sheet2

Norie
06-30-2007, 10:09 PM
Eh, there isn't a Sheet2 in your attachment.:)

Do you mean the sheet called Rpt_Dare?

mdmackillop
07-01-2007, 05:19 AM
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

tqm1
07-01-2007, 10:35 PM
Run-Time Error '1004'
Method 'Range' of object worksheet' failed.
and debug shows error in following two lines

Range(.Cells(1, 12), .Cells(1, 12).End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=.Cells(2, 12), CopyToRange:=.Cells(1, 26), Unique:=True

Please modify

mdmackillop
07-01-2007, 11:34 PM
Try recording a macro to carry out the operation and compare the codes.

tqm1
07-02-2007, 12:21 AM
But I do not know how to adjust more than one criteria in Advance Filter.
Please help at this final touch.

mdmackillop
07-02-2007, 02:00 PM
This works for me. What version of Excel are you using?

rbrhodes
07-03-2007, 12:45 AM
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
07-03-2007, 01:23 AM
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

'Advanced filter
Range(Cells(1, 4), Cells(1, 4).End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:=Cells(2, 12), CopyToRa


Now nothing is appear in combobox.

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

Please help again

Aussiebear
07-03-2007, 02:23 AM
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
07-03-2007, 04:58 AM
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