MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is a custom sort possible


Posted by Brain Dead on May 25, 2001 9:09 PM

Hope someone out there can steer
me in the right direction.

What I need to do is create a custom sort process

Here's the deal :
What I have to do:
>Create a file that generates 70 plus reports sheets).
>Each sheet has to show a different dept vs
product sales for the last 3 years (by mth\by qtr)

Source data :
>I can only download from the system one file per yr
(each year is a separate database - hence one @
time)
>Each file runs about 5,000 lines. Each row
represents a distinct dept \ product sales
(each in a column)

So far :
>I've created a file that :
> Using VBA - Loads the data and then for
each sheet creates a string of dept &
product sales in column A (generates a unique record)
> Using vlookups - created a sheet that pulls
together all of the data, ratios, etcÂ…
for a single department.
> Using VBA - basically created a loop that
runs though a list of all of the depart's
& copies the values to a separate sheet.
Still reading thxs :)

Timing :
> First time I ran the job it took about 1 hour
and 15 minutes
> After much fine-tuning got it down to about
34 minutes.
> One of the best things I did was to
automatically resort the data halfway though
the process so that the data the vlookup
is looking for is nearer the top.

Delimna Still taking too long

Looking for code that will allow me to sort the data
so that the dept that's being processed is @ the top.

I've looked @ dsum, autofilter, etc.. don't think
they'll help.

any & all suggestions are welcome

Thxs


Posted by Ivan Moala on May 26, 2001 12:55 AM

If you are reading & writing large amounts of
data the best way to do this is via memeory,
Data collections and array variants.
That includes sorting as well ie. read data in an
array variant and sort it in the array before writting it to the sheet.

Better if I email you an example.......

Ivan

Posted by Dave Hawley on May 26, 2001 1:00 AM


Hi BD

I would imagine the problem would lie in the code itself. I'm guessing that you have a lot of "Select", "Selection", "ActiveCell" and Copy and Paste, not to mention those horribly slow Loops!.

You can speed code up a bit with the use of Application.screenupading=false
and
Application.Calculation = xlCalculationManual

Don't forget to set back to xlCalculationAutomatic whenever needed!

I spend a lot of my time stressing to my students that loops should only be used in rare cases and when they are used, the data to Loop through should be narrowed down as much as possible via the use of SpecialCells, AutoFilter, AdvancedFilter etc. As a rule of thumb if the Loop is going to be more than 500 to 1000 I will spend considerable time (if necessary) looking for an alternative, nine times out of ten this pays very good dividends!


Go through your code with a critical eye and use properly dimensioned variables. Do not activate cells or Worksheets, this is not necessary in most cases. Make full use of Excels built in features such as the ones mentioned above. The "Find" Method itself is about 100 times quicker than most loops! A simple (or complex)WorsheetFunction placed within 5000 Rows of a Inserted Column in one go, can be used to return say Text for a wanted entry and a Number for non wanted entries. Then you simply Set a Range Variable to SpecialCells of the Text kind.

I have "Enhanced" many projects in Excel VBA for clients and just by following these rules I'm more often than not able to make the code far more iffecient and quicker.


DaveOzGrid Business Applications

Posted by Aladin Akyurek on May 26, 2001 1:46 AM

TERRIBLY CURIOUS ABOUT...

:: So far :

THINGS THAT YOU STRING TOGETHER TO CREATE A UNIQUE RECORD

AND THE VLOOKUP FORMULA THAT YOU USE.

I'D APPRECIATE IF YOU WOULD WANT TO TAKE TIME TO MEET MY CURIOSITY.

THANKS.

Aladin

Posted by Brain Dead on May 26, 2001 8:37 AM

David :

Here's the code that I wrote :
ScreenUpdating is turned off except when to
processing stats - very small area and always on
same sheet

Calculate is set to manual.

The sorting is done via a separte macro and is
called as needed. Attempt to keep the macro code short
and can be used by other macros when needed.

As always thxs for the tips \ suggestions.
BD

Sub All_job_processing()
'
Dim hier_sht As String
Dim hier_key As Variant, hier_title As Variant
Dim total_key As Variant, total_title As Variant
Dim post_here As Variant
Dim resort_trigger As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.Goto Reference:="Main_Home"
Set homesheet = ActiveSheet

On Error GoTo me_bad_all_processing

'Start Timer
Application.Goto Reference:="All_Start_clock"
ActiveCell.FormulaR1C1 = "=NOW()"
With Selection
.NumberFormat = "h:mm:ss AM/PM"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

'Clear out misc triggers
Range("All_Load_cell").ClearContents
Range("All_Job_count").ClearContents
Range("Resort_order").FormulaR1C1 = "Descend"

Range("All_Load_cell") = Application.InputBox(prompt:= _
"Enter the cell reference where data is to be copied {ex BA3}", Type:=2)
post_here = CStr(Range("All_Load_cell"))

Application.Goto Reference:="Process_All"

'Create a value that will trigger the resort @ midpoint
r = ActiveCell.Row
c = ActiveCell.Column
Cells(r, c).Select
Do Until Cells(r, c) = Empty
resort_trigger = resort_trigger + 1
r = r + 1
Loop
Range("All_Job").FormulaR1C1 = resort_trigger


Application.Goto Reference:="Process_All"
r1 = ActiveCell.Row
c1 = ActiveCell.Column
Cells(r1, c1).Select
'Creates a variable so that end of macro - Processor tab
' is reset use total
total_key = ActiveCell.Offset(0, 2)
total_title = ActiveCell.Offset(0, 3)

'Establishes the resort value trigger
resort_trigger = resort_trigger / 2
resort_trigger = resort_trigger + r1

Do Until Cells(r1, c1) = Empty
' A litte code to run stats updating - Always on homesheet
Application.ScreenUpdating = True
Range("All_Job_count") = Range("All_Job_count") + 1
Range("All_Recal_stats").Calculate
Range("Current_Time").Calculate
Application.ScreenUpdating = False

Cells(r1, c1).Select

If ActiveCell.Offset(0, 1) = "X" _
Or ActiveCell.Offset(0, 1) = "x" Then
GoTo skip_job

Else
hier_sht = ActiveCell.Value
hier_key = ActiveCell.Offset(0, 2)
hier_title = ActiveCell.Offset(0, 3)
Application.Goto Reference:="Key"
Range("Key").FormulaR1C1 = hier_key
Range("Title").FormulaR1C1 = hier_title
ActiveSheet.Calculate

Application.Wait Now() + TimeValue("00:00:01") ' Wait a sec

Application.Goto Reference:="All_Data_Copy"
Selection.Copy
Sheets(hier_sht).Select
Range(post_here).Select
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Range("A1").Select

skip_job:
End If
homesheet.Select
r1 = r1 + 1

If r1 = resort_trigger Then
Call Reorder_sort
End If
Loop

'Stop clock
Application.Goto Reference:="All_Stop_clock"
ActiveCell.FormulaR1C1 = "=NOW()"
With Selection
.NumberFormat = "h:mm:ss AM/PM"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

'Put data back in ascending order & puts total
' back on Processor Tab - Speeds up recal during saving file
Range("Resort_order").ClearContents
Call Reorder_sort
Range("Key").FormulaR1C1 = total_key
Range("Title").FormulaR1C1 = total_title

'Wrap Up
homesheet.Select
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlManual
MsgBox ("Done Processing All Data - Check processing time. - Autocal turned off")
Exit Sub

me_bad_all_processing:
MsgBox "All Data processing macro not working"
End Sub

Sub Reorder_sort()

Dim data_range As Variant, flip As Variant
Dim wk_sht As Worksheet
Dim hide_sht As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo me_bad_sort

' Sheets that hold the data are hidden - prefer no one plays with them
For Each wk_sht In ActiveWorkbook.Worksheets
wk_sht.Visible = True
Next wk_sht

Application.Goto Reference:="Load_Start"
r1 = ActiveCell.Row
c1 = ActiveCell.Column
Cells(r1, c1).Select

'Creates a loop to resort data
Do Until Cells(r1, c1) = Empty
'Selects the tab to be sorted
Cells(r1, c1).Select
data_range = ActiveCell.Offset(0, 1)
hide_sht = ActiveCell.Offset(0, 1).Value

Application.Goto Reference:=data_range
flip = Range("Resort_order")
If flip = "Descend" Then
'Sort in Descending order
Selection.sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, Orientation:=xlTopToBottom
Else
'Sort in Ascending order
Selection.sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, Orientation:=xlTopToBottom
End If

Range("A1").Select
Application.Goto Reference:="Main_Home"
Sheets(hide_sht).Visible = xlHidden
r1 = r1 + 1
Loop

Application.Goto Reference:="Main_Home"
Application.Calculation = xlManual
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
me_bad_sort:
MsgBox "Sort Marco not working"
End Sub

Posted by Brain Dead on May 26, 2001 8:48 AM

Disregard prior post - Add a comment :Current code : For

David :

Here's the code that I wrote :
ScreenUpdating is turned off except when to
processing stats - very small area and always on
same sheet

Calculate is set to manual.

The sorting is done via a separte macro and is
called as needed. Attempt to keep the macro code short
and can be used by other macros when needed.

As always thxs for the tips \ suggestions.
BD

PS - all loops are about 100 times. Anything more
and I'd shoot myself :)

Sub All_job_processing()
'
Dim hier_sht As String
Dim hier_key As Variant, hier_title As Variant
Dim total_key As Variant, total_title As Variant
Dim post_here As Variant
Dim resort_trigger As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.Goto Reference:="Main_Home"
Set homesheet = ActiveSheet

On Error GoTo me_bad_all_processing

'Start Timer
Application.Goto Reference:="All_Start_clock"
ActiveCell.FormulaR1C1 = "=NOW()"
With Selection
.NumberFormat = "h:mm:ss AM/PM"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

'Clear out misc triggers
Range("All_Load_cell").ClearContents
Range("All_Job_count").ClearContents
Range("Resort_order").FormulaR1C1 = "Descend"

Range("All_Load_cell") = Application.InputBox(prompt:= _
"Enter the cell reference where data is to be copied {ex BA3}", Type:=2)
post_here = CStr(Range("All_Load_cell"))

Application.Goto Reference:="Process_All"

'Create a value that will trigger the resort @ midpoint
r = ActiveCell.Row
c = ActiveCell.Column
Cells(r, c).Select
Do Until Cells(r, c) = Empty
resort_trigger = resort_trigger + 1
r = r + 1
Loop
Range("All_Job").FormulaR1C1 = resort_trigger


Application.Goto Reference:="Process_All"
r1 = ActiveCell.Row
c1 = ActiveCell.Column
Cells(r1, c1).Select
'Creates a variable so that end of macro - Processor tab
' is reset use total
total_key = ActiveCell.Offset(0, 2)
total_title = ActiveCell.Offset(0, 3)

'Establishes the resort value trigger
resort_trigger = resort_trigger / 2
resort_trigger = resort_trigger + r1

Do Until Cells(r1, c1) = Empty
' A litte code to run stats updating - Always on homesheet
Application.ScreenUpdating = True
Range("All_Job_count") = Range("All_Job_count") + 1
Range("All_Recal_stats").Calculate
Range("Current_Time").Calculate
Application.ScreenUpdating = False

Cells(r1, c1).Select

If ActiveCell.Offset(0, 1) = "X" _
Or ActiveCell.Offset(0, 1) = "x" Then
GoTo skip_job

Else
hier_sht = ActiveCell.Value
hier_key = ActiveCell.Offset(0, 2)
hier_title = ActiveCell.Offset(0, 3)
Application.Goto Reference:="Key"
Range("Key").FormulaR1C1 = hier_key
Range("Title").FormulaR1C1 = hier_title
ActiveSheet.Calculate

Application.Wait Now() + TimeValue("00:00:01") ' Wait a sec

Application.Goto Reference:="All_Data_Copy"
Selection.Copy
Sheets(hier_sht).Select
Range(post_here).Select
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Range("A1").Select

skip_job:
End If
homesheet.Select
r1 = r1 + 1

If r1 = resort_trigger Then
Call Reorder_sort
End If
Loop

'Stop clock
Application.Goto Reference:="All_Stop_clock"
ActiveCell.FormulaR1C1 = "=NOW()"
With Selection
.NumberFormat = "h:mm:ss AM/PM"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

'Put data back in ascending order & puts total
' back on Processor Tab - Speeds up recal during saving file
Range("Resort_order").ClearContents
Call Reorder_sort
Range("Key").FormulaR1C1 = total_key
Range("Title").FormulaR1C1 = total_title

'Wrap Up
homesheet.Select
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlManual
MsgBox ("Done Processing All Data - Check processing time. - Autocal turned off")
Exit Sub

me_bad_all_processing:
MsgBox "All Data processing macro not working"
End Sub

Sub Reorder_sort()

Dim data_range As Variant, flip As Variant
Dim wk_sht As Worksheet
Dim hide_sht As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo me_bad_sort

' Sheets that hold the data are hidden - prefer no one plays with them
For Each wk_sht In ActiveWorkbook.Worksheets
wk_sht.Visible = True
Next wk_sht

Application.Goto Reference:="Load_Start"
r1 = ActiveCell.Row
c1 = ActiveCell.Column
Cells(r1, c1).Select

'Creates a loop to resort data
Do Until Cells(r1, c1) = Empty
'Selects the tab to be sorted
Cells(r1, c1).Select
data_range = ActiveCell.Offset(0, 1)
hide_sht = ActiveCell.Offset(0, 1).Value

Application.Goto Reference:=data_range
flip = Range("Resort_order")
If flip = "Descend" Then
'Sort in Descending order
Selection.sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, Orientation:=xlTopToBottom
Else
'Sort in Ascending order
Selection.sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, Orientation:=xlTopToBottom
End If

Range("A1").Select
Application.Goto Reference:="Main_Home"
Sheets(hide_sht).Visible = xlHidden
r1 = r1 + 1
Loop

Application.Goto Reference:="Main_Home"
Application.Calculation = xlManual
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
me_bad_sort:
MsgBox "Sort Marco not working"
End Sub

Posted by Brain Dead on May 26, 2001 9:09 AM

Aladin :

"THINGS THAT YOU STRING TOGETHER TO CREATE A UNIQUE RECORD"
On each sheet that holds data in column A1 have

Col_A Col_H Col_K
Dept product
=H1&K1 ABC...... Widgets
=H100&K1000 CDA...... Widgets

so that in Col_A
A1 = ABC......Widgets
A1000 = CDA......Widgets

"AND THE VLOOKUP FORMULA THAT YOU USE."
On the processing tab sample vlookup in cell c12

IF(ISNA(VLOOKUP($C$5&$A12,INDIRECT(C$1),C$4,FALSE)
),0,(VLOOKUP($C$5&$A12,INDIRECT(C$1),C$4,FALSE)))

$C$5= contains the Dept name (ABC......)
$A12= contains the product (widgets)
C$1 = range name on the data sheets
C$4 = Contains the col # to be returned

"I'D APPRECIATE IF YOU WOULD WANT TO TAKE TIME TO MEET MY CURIOSITY."

No I believe I owe you thanks -
Any tips or suggestions of course are always
welcome
BD

TERRIBLY CURIOUS ABOUT... :: So far :

Posted by Aladin Akyurek on May 26, 2001 10:19 AM

What if?

BD,

I have a proposal regarding your VLOOKUP formula. IF(ISNA(VLOOKUP($C$5&$A12,INDIRECT(C$1),C$4,FALSE)

If you could compute the address of the first column of the table INDIRECT(C$1)refers to, say in C2 and re-write the vlookup formula as follows:

=IF(ISNUMBER(MATCH($C$5&$A12,INDIRECT(C$2),0), VLOOKUP($C$5&$A12,INDIRECT(C$1),C$4,0),0)

do you get any improvement qua performance? The hypothesis is that it should.

By the way, 0=FALSE.

Since you sort your data to help vlookup to find lookup values faster, I wonder whether it is possible with your data to omit the 4th arg (that is, 0 or FALSE). I remember Mark W. making a statement in that direction.

Still curious.

Aladin

Posted by Sean on May 26, 2001 3:22 PM

Posted by Brain Dead on May 26, 2001 9:43 PM

Re: What if?

Aladin :

Will try on Monday - taking Sunday off. I've seen
that proposal before & have tried it. To be frank
I didn't see any time savings. But will try it
again.

After much playing with the code got the processing time
down from an hour & 15 minutes to around 13 minutes.

To get there, basically I've set up a loop that
resorts the data during each processing cycle -
so that the data the vlookup is looking for
is always near the top. :)

To do this I've set up a custom list which is
deleted and recreated during each cycle.

Does anyone out there know how to incorporate
a wildcard into a custom list ?

Also is there anyway to name a list - currently
the appication gives it a # of 5. Like to figure
out how to be able to specifically use a "named"
list so that I can make sure I'm deleting and using
the specific list I want to use.

As always any input \ tips are welcome.
thxs
BD BD, I have a proposal regarding your VLOOKUP formula. : IF(ISNA(VLOOKUP($C$5&$A12,INDIRECT(C$1),C$4,FALSE)

Posted by Dave Hawley on May 26, 2001 10:50 PM

Hi BD

Looking at your code I can see why it is so slow. You have a lot of Loops that should not be there. You also have a lot of "Select", "Selection", "Activecell" and "GoTo".

While the Macro recorder is a great tool for learning VBA (when used correctly) it is horribly inefficient in writing code. You must remember that to Recorder ONLY records your steps taken in the Interface, it doesn't write the VBA code that SHOULD be used to achieve these steps.

The Loops are a trap all to common to most VBA coders, I often say "the coder him/her self often is the one caught in an endless loop!". It always seems to happen that once a programmer gets the general gist of Loops, they use them FAR to often, as they offer a "quick fix". I liken them very closely to the 'Array formula trap' in the Excel Interface. ie; Once a user learns one, they start applying them without thought, again this is because of the "quick fix". Then end result is nearly always the same, eg; months of work wasted because of poor performance (sometimes unusable).

The best advice I can offer is "Step outside the box" and look for other means of achieving your desired result. Remember it is very rarely necessary to use "Select", "Selection" etc. Above all though USE Excels built in Features and Functions, they cannot be beaten by any VBA code in nearly ALL cases.


You seem to think that your problem my lie in the recalculation being slow. You can test this very easily by simply pushing F9. In most cases it should happen within 1-2 seconds. If it takes greater than 5 seconds you probably have a problem with your formulas ( arrays ?).


You may not want to hear all this (most don't), I am only trying to look at your situation from an objective point of view. I believe it is better to eliminate bad habits now and replace them with GOOD ones. Good habits are just as hard to break as bad ones!


Below is a small modification the start of your code that hopefully will show you that there is often a "Better way".


Sub All_job_processing()
Dim rRange As Range
Dim hier_sht As String
Dim hier_key As Variant, hier_title As Variant
Dim total_key As Variant, total_title As Variant
Dim post_here As Variant
Dim resort_trigger As Integer
Dim r As Long
Dim c As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.Goto Reference:="Main_Home"
Set homesheet = ActiveSheet

On Error GoTo me_bad_all_processing

'Start Timer
With Range("All_Start_clock")
.Formula = "=NOW()"
.NumberFormat = "h:mm:ss AM/PM"
End With
Range("All_Start_clock") = Range("All_Start_clock").Value

'Clear out misc triggers
Range("All_Load_cell").ClearContents
Range("All_Job_count").ClearContents
Range("Resort_order").FormulaR1C1 = "Descend"

'Range("All_Load_cell")
On Error Resume Next
Set rRange = Application.InputBox(prompt:= _
"Select the cell reference where data is to be copied {ex BA3}", Type:=8)
If rRange Is Nothing Then Exit Sub

'post_here = CStr(Range("All_Load_cell"))

r = Range("Process_All").Cells(1, 1).Row

'Create a value that will trigger the resort @ midpoint
resort_trigger = _
Range("Process_All").End(xlDown).Offset(1, 0).Row - r

Range("All_Job").Value = resort_trigger


'Creates a variable so that end of macro - Processor tab
' is reset use total
total_key = Range("Process_All").Cells(1, 3)
total_key = Range("Process_All").Cells(1, 4)

'Establishes the resort value trigger
resort_trigger = resort_trigger / 2
resort_trigger = resort_trigger + r1

Do Until Cells(r1, c1) = Empty
' A litte code to run stats updating - Always on homesheet
Application.ScreenUpdating = True
Range("All_Job_count") = Range("All_Job_count") + 1
Range("All_Recal_stats").Calculate
Range("Current_Time").Calculate
Application.ScreenUpdating = False

Cells(r1, c1).Select

If ActiveCell.Offset(0, 1) = "X" _
Or ActiveCell.Offset(0, 1) = "x" Then
GoTo skip_job

Else
hier_sht = ActiveCell.Value
hier_key = ActiveCell.Offset(0, 2)
hier_title = ActiveCell.Offset(0, 3)
Application.Goto Reference:="Key"
Range("Key").FormulaR1C1 = hier_key
Range("Title").FormulaR1C1 = hier_title
ActiveSheet.Calculate

Application.Wait Now() + TimeValue("00:00:01") ' Wait a sec

Application.Goto Reference:="All_Data_Copy"
Selection.Copy
Sheets(hier_sht).Select
Range(post_here).Select
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Range("A1").Select

skip_job:
End If
homesheet.Select
r1 = r1 + 1

If r1 = resort_trigger Then
Call Reorder_sort
End If
Loop

'Stop clock
Application.Goto Reference:="All_Stop_clock"
ActiveCell.FormulaR1C1 = "=NOW()"
With Selection
.NumberFormat = "h:mm:ss AM/PM"
.Copy
.PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = False

'Put data back in ascending order & puts total
' back on Processor Tab - Speeds up recal during saving file
Range("Resort_order").ClearContents
Call Reorder_sort
Range("Key").FormulaR1C1 = total_key
Range("Title").FormulaR1C1 = total_title

'Wrap Up
homesheet.Select
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlManual
MsgBox ("Done Processing All Data - Check processing time. - Autocal turned off")
Exit Sub

me_bad_all_processing:
MsgBox "All Data processing macro not working"
End Sub

Sub Reorder_sort()

Dim data_range As Variant, flip As Variant
Dim wk_sht As Worksheet
Dim hide_sht As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error GoTo me_bad_sort

' Sheets that hold the data are hidden - prefer no one plays with them
For Each wk_sht In ActiveWorkbook.Worksheets
wk_sht.Visible = True
Next wk_sht

Application.Goto Reference:="Load_Start"
r1 = ActiveCell.Row
c1 = ActiveCell.Column
Cells(r1, c1).Select

'Creates a loop to resort data
Do Until Cells(r1, c1) = Empty
'Selects the tab to be sorted
Cells(r1, c1).Select
data_range = ActiveCell.Offset(0, 1)
hide_sht = ActiveCell.Offset(0, 1).Value

Application.Goto Reference:=data_range
flip = Range("Resort_order")
If flip = "Descend" Then
'Sort in Descending order
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, Orientation:=xlTopToBottom
Else
'Sort in Ascending order
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, Orientation:=xlTopToBottom
End If

Range("A1").Select
Application.Goto Reference:="Main_Home"
Sheets(hide_sht).Visible = xlHidden
r1 = r1 + 1
Loop

Application.Goto Reference:="Main_Home"
Application.Calculation = xlManual
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
me_bad_sort:
MsgBox "Sort Marco not working"
End Sub

Dave


OzGrid Business Applications

Posted by Dave Hawley on May 26, 2001 10:55 PM

...Change the start of your code to:

Range("All_Start_clock") = Format(Time, "h:mm:ss AM/PM")


Dave

OzGrid Business Applications

Posted by Brain Dead on May 28, 2001 5:10 PM

Re: What if?

Aladin :

Spent the last 2 hours trying to get this working -
Wasn't succesful. Keep getting "N\A" msgs

When you say - calculate address of first column -
???. This might be where I'm hanging
e

Posted by Brain Dead on May 28, 2001 8:20 PM

Re: What if?

Aladin:

Finally got it working -
I'll admit - cheated a bit.
But taking all that into account -
I'm sorry to say, that I don't see
or expect see any measureable
improvement in performance

Was hoping this would be a new approach

BD BD, I have a proposal regarding your VLOOKUP formula. : IF(ISNA(VLOOKUP($C$5&$A12,INDIRECT(C$1),C$4,FALSE)

Posted by Brain Dead on May 28, 2001 8:21 PM

Perfect !!!!!!!!!!!! :)

Works perfectly

Posted by Aladin Akyurek on May 29, 2001 1:26 AM

Re: What if?

Hi BD,

Many thanks for testing. Not too happy to hear that it doesn't matter qua performance whether you use either of

=IF(ISNA(VLOOKUP(...),return-value,VLOOKUP(...))
=IF(ISNUMBER(MATCH(...),VLOOKUP(...),return-value)

It seems MS programmers didn't anything silly in coding of VLOOKUP.

The performance should be better if there was no need for computing twice that both formulas carry out. I've made a proposal to MS to change the syntax of lookup functions. I hope they'll implement it for the proposal eliminates computing twice.

PS. I'll keep using the second form, because it looks nicer to me. :)

Thanks again.

Aladin Aladin: Finally got it working -