Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jack Sons
 
Posts: n/a
Default sorting, borders going with cell content

Hi all,

When I sort a column the order of cells will be rearranged, see this code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _
'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

Instead of trying to keep the existing borders, could you remove the borders and
reapply?

I would think that this would be more straightforward (er, pronounced easier).

Jack Sons wrote:

Hi all,

When I sort a column the order of cells will be rearranged, see this code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _
'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3

will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands


--

Dave Peterson
  #3   Report Post  
David McRitchie
 
Posts: n/a
Default

I agree with Dave Paterson, but I'd really suggest some other choices.

There are eight borders around a cell (inside and outside), as you already
mentioned the cell borders remain in place when sorting, it would be
impossible for Excel to know how borders are the interact with other
borders if it tried to keep them intact. (pretty picture...)
http://www.mvps.org/dmcritchie/excel/sorting.htm

Each combination of formatting attributes counts toward a limit so
you really don't want to mess with borders if you an help it, keep
them all the same color.

Would suggest you use another means to differentiate your cells
such as Bold, font color, Interior color. If you are already using
all of those things you probably have a very active worksheet, and
might want to tone it down. There is also pattern shading but I find that
very hard to read, and having a laptop almost definitely exasperates
any problems with coloring. Don't forget about Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
in fact you can change the borders with C.F.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" wrote in message ...
Instead of trying to keep the existing borders, could you remove the borders and
reapply?

I would think that this would be more straightforward (er, pronounced easier).

Jack Sons wrote:

Hi all,

When I sort a column the order of cells will be rearranged, see this code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _
'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3

will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands


--

Dave Peterson



  #4   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells that
belong together have a thick horizontal or vertical borderline in common,
like below (the vertical should be an uninterrupted line). As an exemple
this range could typically be something like A2503:H2558 with the horizontal
line above A2503:H2503 and the vertical line to the right of D2503:D2558.
In the sorting process the whole range A2503:H2503 has to be moved to
elsewhere (with the "lines") without leaviing a trace because its borders
will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands



  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

I think you'd have to use the same rules to reapply the borders as you did when
you first applied the borders.

So if you add bottom/top borders between cells in adjacent rows that change
values, you could look for changes.

Jack Sons wrote:

Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells that
belong together have a thick horizontal or vertical borderline in common,
like below (the vertical should be an uninterrupted line). As an exemple
this range could typically be something like A2503:H2558 with the horizontal
line above A2503:H2503 and the vertical line to the right of D2503:D2558.
In the sorting process the whole range A2503:H2503 has to be moved to
elsewhere (with the "lines") without leaviing a trace because its borders
will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|

Any suggestions for code?

Jack.

"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands


--

Dave Peterson


  #6   Report Post  
David McRitchie
 
Posts: n/a
Default

You are not describing what would have to be done.
You will have to either start from scratch each time using a macro,
possibly with a helper column also involved, or perhaps some
Conditional Formatting. You cannot make the sort do something
that it does not do. As stated before, if you do a lot of creative
cell border formatting you may cause trouble with

Cell styles in a workbook specification limit of 4,000

and you really don't want to make that happen.
---

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jack Sons" wrote in message ...
Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells that
belong together have a thick horizontal or vertical borderline in common,
like below (the vertical should be an uninterrupted line). As an exemple
this range could typically be something like A2503:H2558 with the horizontal
line above A2503:H2503 and the vertical line to the right of D2503:D2558.
In the sorting process the whole range A2503:H2503 has to be moved to
elsewhere (with the "lines") without leaviing a trace because its borders
will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands





  #7   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave and David,

I understand.

The problem would not arise if in my code

..............
..............
MyNames = Dir("*.xls")
Do while .........
..............
..............
Dir
loop
..............
..............

would call the files in the current directory (with order as seen below)

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

in the order of occurrence in stead of

1066.xls
123.xls
1230.xls
1363.xls
2155.xls
2308.xls
25.xls
333.xls
763.xls
8.xls

This last order seems "logical" to Excel - I understand that - but not to
normal humans. I can't change the names of the files nor the sequence of the
files in the directory which happens to be the numerical sequence that is
"logical" to humans.

Is there a way to call the files (allmost a thousand files) one by one
corresponding to the sequence in their directory?

If so, my problem would be over!

Jack.

"Jack Sons" schreef in bericht
...
Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells that
belong together have a thick horizontal or vertical borderline in common,
like below (the vertical should be an uninterrupted line). As an exemple
this range could typically be something like A2503:H2558 with the
horizontal line above A2503:H2503 and the vertical line to the right of
D2503:D2558. In the sorting process the whole range A2503:H2503 has to be
moved to elsewhere (with the "lines") without leaviing a trace because its
borders will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this
code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands





  #8   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Jack,
Place the path w/o the filename in another column so
you can sort on that. Sorting the numbers is simple
in fact you could just remove the .xls in a helper column,
or use Text to columns separating at the period, and then
use the trimall macro on my join.htm page to reenter so they
become numbers if they didn't automatically become numbers.

But for other similar things you could look at
http://www.mvps.org/dmcritchie/excel...htm#normdigits
and normalize at 5 digits, and at
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jack Sons" wrote in message ...
Dave and David,

I understand.

The problem would not arise if in my code

.............
.............
MyNames = Dir("*.xls")
Do while .........
.............
.............
Dir
loop
.............
.............

would call the files in the current directory (with order as seen below)

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

in the order of occurrence in stead of

1066.xls
123.xls
1230.xls
1363.xls
2155.xls
2308.xls
25.xls
333.xls
763.xls
8.xls

This last order seems "logical" to Excel - I understand that - but not to
normal humans. I can't change the names of the files nor the sequence of the
files in the directory which happens to be the numerical sequence that is
"logical" to humans.

Is there a way to call the files (allmost a thousand files) one by one
corresponding to the sequence in their directory?

If so, my problem would be over!

Jack.

"Jack Sons" schreef in bericht
...
Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells that
belong together have a thick horizontal or vertical borderline in common,
like below (the vertical should be an uninterrupted line). As an exemple
this range could typically be something like A2503:H2558 with the
horizontal line above A2503:H2503 and the vertical line to the right of
D2503:D2558. In the sorting process the whole range A2503:H2503 has to be
moved to elsewhere (with the "lines") without leaviing a trace because its
borders will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this
code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands







  #9   Report Post  
Dave Peterson
 
Posts: n/a
Default

I'm not sure how windows actually stores the file, but I don't think it does it
alphabetical. You may be viewing in alphabetical order via Windows|Explorer,
but you can change that display just by clicking on the title in detail view.
(This doesn't change the order of how the files are saved by the operating
system.)

But if your files are always ###.xls (no leading 0's and always numeric), then
you could go get all the names and do the sort yourself. I used a QSort
procedure that I saved from a newsgroup post (sorry about not remembering the
author/authors.)

Option Explicit
Sub testme01()

Dim myNames() As Long
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim JustName As String

'change to point at the folder to check
myPath = "C:\My Documents\Excel\test"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
'keep only the numeric filenames
JustName = Left(myFile, Len(myFile) - 4)
If IsNumeric(JustName) Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = CLng(JustName)
End If
myFile = Dir()

Loop

If fCtr = 0 Then
MsgBox "No Numeric Files Found!"
Exit Sub
End If

Call qSort(myNames)

For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.
MsgBox myPath & myNames(fCtr) & ".xls"
Next fCtr

End Sub

Public Sub qSort(v, Optional n& = True, Optional m& = True)
Dim i&, j&, p, t
If n = True Then n = LBound(v): If m = True Then m = UBound(v)
i = n: j = m: p = v((n + m) \ 2)
While (i <= j)
While (v(i) < p And i < m): i = i + 1: Wend
While (v(j) p And j n): j = j - 1: Wend
If (i <= j) Then
t = v(i): v(i) = v(j): v(j) = t
i = i + 1: j = j - 1
End If
Wend
If (n < j) Then qSort v, n, j
If (i < m) Then qSort v, i, m
End Sub


Jack Sons wrote:

Dave and David,

I understand.

The problem would not arise if in my code

.............
.............
MyNames = Dir("*.xls")
Do while .........
.............
.............
Dir
loop
.............
.............

would call the files in the current directory (with order as seen below)

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

in the order of occurrence in stead of

1066.xls
123.xls
1230.xls
1363.xls
2155.xls
2308.xls
25.xls
333.xls
763.xls
8.xls

This last order seems "logical" to Excel - I understand that - but not to
normal humans. I can't change the names of the files nor the sequence of the
files in the directory which happens to be the numerical sequence that is
"logical" to humans.

Is there a way to call the files (allmost a thousand files) one by one
corresponding to the sequence in their directory?

If so, my problem would be over!

Jack.

"Jack Sons" schreef in bericht
...
Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells that
belong together have a thick horizontal or vertical borderline in common,
like below (the vertical should be an uninterrupted line). As an exemple
this range could typically be something like A2503:H2558 with the
horizontal line above A2503:H2503 and the vertical line to the right of
D2503:D2558. In the sorting process the whole range A2503:H2503 has to be
moved to elsewhere (with the "lines") without leaviing a trace because its
borders will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this
code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value, text
color, italic, bold etc. and even fill color, but not the cell borders.

I really need code to sort with which also the cell borders will change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands




--

Dave Peterson
  #10   Report Post  
Dave Peterson
 
Posts: n/a
Default

I googled for "Sub qSort" and keepitcool has posted this several times.

Dave Peterson wrote:
<<snipped
I used a QSort

procedure that I saved from a newsgroup post (sorry about not remembering the
author/authors.)


<<snipped


  #11   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave,

I tried to do as you said.
Now I have this piece of code:

Call qSort(myNames)
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.
'------------------------------------------------------------------------
Set mybook = Workbooks.Open(myNames)

I think after Call qSort(myNames) myNames is an array sorted like

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

(is that called numerical in contrast to alphabetical, is alphabetical the
order 1066.xls 123.xls 1230.xls .... 25.xls ..... 8.xls?).

My next line of code should open the file indicated by the first element of
array myNames, so 8.xls. This won't work because - I suppose -
Workbooks.Open does not accept an array.

I think I am close to the solution, so please help me further.

Jack.

"Dave Peterson" schreef in bericht
...
I googled for "Sub qSort" and keepitcool has posted this several times.

Dave Peterson wrote:
<<snipped
I used a QSort

procedure that I saved from a newsgroup post (sorry about not remembering
the
author/authors.)


<<snipped



  #12   Report Post  
Jack Sons
 
Posts: n/a
Default

David,

In fact I already have a helper column with the "naked" filenumbers 8 25
123 333 ....etc. and I did sort on that column.
The first is the file corresponding to 1066.xls because Windows/Excel "sees"
ore stores 1066 before 123 because it does so in the alphabetical in stead
of the numerical order (I hope I said it right).

Along all rows of

_________________________________ 1066
| 1066
| 1066
| 1066
| 1066

in column I, so in all cells of column I "belonging" to the part that was
originally 8.xls, there is the number 1066. After that comes
_________________________________ 123
| 123
| 123
| 123
| 123

Because of this I have to sort (numerically) and there the problems arise:
the borderlines stay where the are.

I am now trying out what Dave advised in his last answer.


"David McRitchie" schreef in bericht
...
Hi Jack,
Place the path w/o the filename in another column so
you can sort on that. Sorting the numbers is simple
in fact you could just remove the .xls in a helper column,
or use Text to columns separating at the period, and then
use the trimall macro on my join.htm page to reenter so they
become numbers if they didn't automatically become numbers.

But for other similar things you could look at
http://www.mvps.org/dmcritchie/excel...htm#normdigits
and normalize at 5 digits, and at
Extraction of a Group of Digits and Dashes, from postings by Harlan
Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jack Sons" wrote in message
...
Dave and David,

I understand.

The problem would not arise if in my code

.............
.............
MyNames = Dir("*.xls")
Do while .........
.............
.............
Dir
loop
.............
.............

would call the files in the current directory (with order as seen below)

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

in the order of occurrence in stead of

1066.xls
123.xls
1230.xls
1363.xls
2155.xls
2308.xls
25.xls
333.xls
763.xls
8.xls

This last order seems "logical" to Excel - I understand that - but not to
normal humans. I can't change the names of the files nor the sequence of
the
files in the directory which happens to be the numerical sequence that is
"logical" to humans.

Is there a way to call the files (allmost a thousand files) one by one
corresponding to the sequence in their directory?

If so, my problem would be over!

Jack.

"Jack Sons" schreef in bericht
...
Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells
that
belong together have a thick horizontal or vertical borderline in
common,
like below (the vertical should be an uninterrupted line). As an
exemple
this range could typically be something like A2503:H2558 with the
horizontal line above A2503:H2503 and the vertical line to the right of
D2503:D2558. In the sorting process the whole range A2503:H2503 has to
be
moved to elsewhere (with the "lines") without leaviing a trace because
its
borders will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this
code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value,
text
color, italic, bold etc. and even fill color, but not the cell
borders.

I really need code to sort with which also the cell borders will
change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands









  #13   Report Post  
Dave Peterson
 
Posts: n/a
Default

If you did text to columns, make sure you told excel that that field was General
(not text).

(or select an empty cell
edit|copy
select the range of Text "numbers"
edit|paste special|click Add




Jack Sons wrote:

David,

In fact I already have a helper column with the "naked" filenumbers 8 25
123 333 ....etc. and I did sort on that column.
The first is the file corresponding to 1066.xls because Windows/Excel "sees"
ore stores 1066 before 123 because it does so in the alphabetical in stead
of the numerical order (I hope I said it right).

Along all rows of

_________________________________ 1066
| 1066
| 1066
| 1066
| 1066

in column I, so in all cells of column I "belonging" to the part that was
originally 8.xls, there is the number 1066. After that comes
_________________________________ 123
| 123
| 123
| 123
| 123

Because of this I have to sort (numerically) and there the problems arise:
the borderlines stay where the are.

I am now trying out what Dave advised in his last answer.

"David McRitchie" schreef in bericht
...
Hi Jack,
Place the path w/o the filename in another column so
you can sort on that. Sorting the numbers is simple
in fact you could just remove the .xls in a helper column,
or use Text to columns separating at the period, and then
use the trimall macro on my join.htm page to reenter so they
become numbers if they didn't automatically become numbers.

But for other similar things you could look at
http://www.mvps.org/dmcritchie/excel...htm#normdigits
and normalize at 5 digits, and at
Extraction of a Group of Digits and Dashes, from postings by Harlan
Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jack Sons" wrote in message
...
Dave and David,

I understand.

The problem would not arise if in my code

.............
.............
MyNames = Dir("*.xls")
Do while .........
.............
.............
Dir
loop
.............
.............

would call the files in the current directory (with order as seen below)

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

in the order of occurrence in stead of

1066.xls
123.xls
1230.xls
1363.xls
2155.xls
2308.xls
25.xls
333.xls
763.xls
8.xls

This last order seems "logical" to Excel - I understand that - but not to
normal humans. I can't change the names of the files nor the sequence of
the
files in the directory which happens to be the numerical sequence that is
"logical" to humans.

Is there a way to call the files (allmost a thousand files) one by one
corresponding to the sequence in their directory?

If so, my problem would be over!

Jack.

"Jack Sons" schreef in bericht
...
Dave and David,

In principle I agree.

But I have to sort in a very large file of which many groups of cells
that
belong together have a thick horizontal or vertical borderline in
common,
like below (the vertical should be an uninterrupted line). As an
exemple
this range could typically be something like A2503:H2558 with the
horizontal line above A2503:H2503 and the vertical line to the right of
D2503:D2558. In the sorting process the whole range A2503:H2503 has to
be
moved to elsewhere (with the "lines") without leaviing a trace because
its
borders will not correspond with the cells that wil take its place.

_________________________________
|
|
|
|


Any suggestions for code?

Jack.


"Jack Sons" schreef in bericht
...
Hi all,

When I sort a column the order of cells will be rearranged, see this
code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=False,
Orientation:=xlTopToBottom ', _

'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value,
text
color, italic, bold etc. and even fill color, but not the cell
borders.

I really need code to sort with which also the cell borders will
change
place, I mean they will "stick" to the cell content, so

1
2
3


will result in

3
2
1
Your help will be appreciated.

Jack Sons
The Netherlands








--

Dave Peterson
  #14   Report Post  
Dave Peterson
 
Posts: n/a
Default

For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(mypath & myNames(fctr) & ".xls")

That's what I was trying to show with the msgbox:
MsgBox myPath & myNames(fCtr) & ".xls"

You have to rebuild the name--that's why you couldn't use "0000888.xls" as a
file name and still have it work.


Jack Sons wrote:

Dave,

I tried to do as you said.
Now I have this piece of code:

Call qSort(myNames)
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.
'------------------------------------------------------------------------
Set mybook = Workbooks.Open(myNames)

I think after Call qSort(myNames) myNames is an array sorted like

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

(is that called numerical in contrast to alphabetical, is alphabetical the
order 1066.xls 123.xls 1230.xls .... 25.xls ..... 8.xls?).

My next line of code should open the file indicated by the first element of
array myNames, so 8.xls. This won't work because - I suppose -
Workbooks.Open does not accept an array.

I think I am close to the solution, so please help me further.

Jack.

"Dave Peterson" schreef in bericht
...
I googled for "Sub qSort" and keepitcool has posted this several times.

Dave Peterson wrote:
<<snipped
I used a QSort
procedure that I saved from a newsgroup post (sorry about not remembering
the
author/authors.)


<<snipped


--

Dave Peterson
  #15   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave,

Finally I got it, works like a charm, thanks a lot.
BTW, qSort is a mistery to me.

Jack.


"Dave Peterson" schreef in bericht
...
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(mypath & myNames(fctr) & ".xls")

That's what I was trying to show with the msgbox:
MsgBox myPath & myNames(fCtr) & ".xls"

You have to rebuild the name--that's why you couldn't use "0000888.xls" as
a
file name and still have it work.


Jack Sons wrote:

Dave,

I tried to do as you said.
Now I have this piece of code:

Call qSort(myNames)
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(myNames)

I think after Call qSort(myNames) myNames is an array sorted like

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

(is that called numerical in contrast to alphabetical, is alphabetical
the
order 1066.xls 123.xls 1230.xls .... 25.xls ..... 8.xls?).

My next line of code should open the file indicated by the first element
of
array myNames, so 8.xls. This won't work because - I suppose -
Workbooks.Open does not accept an array.

I think I am close to the solution, so please help me further.

Jack.

"Dave Peterson" schreef in bericht
...
I googled for "Sub qSort" and keepitcool has posted this several times.

Dave Peterson wrote:
<<snipped
I used a QSort
procedure that I saved from a newsgroup post (sorry about not
remembering
the
author/authors.)

<<snipped


--

Dave Peterson





  #16   Report Post  
Dave Peterson
 
Posts: n/a
Default

Just remember where you stored a copy--and use it without thinking.

It works for me!

Jack Sons wrote:

Dave,

Finally I got it, works like a charm, thanks a lot.
BTW, qSort is a mistery to me.

Jack.

"Dave Peterson" schreef in bericht
...
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(mypath & myNames(fctr) & ".xls")

That's what I was trying to show with the msgbox:
MsgBox myPath & myNames(fCtr) & ".xls"

You have to rebuild the name--that's why you couldn't use "0000888.xls" as
a
file name and still have it work.


Jack Sons wrote:

Dave,

I tried to do as you said.
Now I have this piece of code:

Call qSort(myNames)
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(myNames)

I think after Call qSort(myNames) myNames is an array sorted like

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

(is that called numerical in contrast to alphabetical, is alphabetical
the
order 1066.xls 123.xls 1230.xls .... 25.xls ..... 8.xls?).

My next line of code should open the file indicated by the first element
of
array myNames, so 8.xls. This won't work because - I suppose -
Workbooks.Open does not accept an array.

I think I am close to the solution, so please help me further.

Jack.

"Dave Peterson" schreef in bericht
...
I googled for "Sub qSort" and keepitcool has posted this several times.

Dave Peterson wrote:
<<snipped
I used a QSort
procedure that I saved from a newsgroup post (sorry about not
remembering
the
author/authors.)

<<snipped


--

Dave Peterson


--

Dave Peterson
  #17   Report Post  
Jack Sons
 
Posts: n/a
Default

All right, I am good at that.

Jack.


"Dave Peterson" schreef in bericht
...
Just remember where you stored a copy--and use it without thinking.

It works for me!

Jack Sons wrote:

Dave,

Finally I got it, works like a charm, thanks a lot.
BTW, qSort is a mistery to me.

Jack.

"Dave Peterson" schreef in bericht
...
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(mypath & myNames(fctr) & ".xls")

That's what I was trying to show with the msgbox:
MsgBox myPath & myNames(fCtr) & ".xls"

You have to rebuild the name--that's why you couldn't use "0000888.xls"
as
a
file name and still have it work.


Jack Sons wrote:

Dave,

I tried to do as you said.
Now I have this piece of code:

Call qSort(myNames)
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(myNames)

I think after Call qSort(myNames) myNames is an array sorted like

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

(is that called numerical in contrast to alphabetical, is alphabetical
the
order 1066.xls 123.xls 1230.xls .... 25.xls ..... 8.xls?).

My next line of code should open the file indicated by the first
element
of
array myNames, so 8.xls. This won't work because - I suppose -
Workbooks.Open does not accept an array.

I think I am close to the solution, so please help me further.

Jack.

"Dave Peterson" schreef in bericht
...
I googled for "Sub qSort" and keepitcool has posted this several
times.

Dave Peterson wrote:
<<snipped
I used a QSort
procedure that I saved from a newsgroup post (sorry about not
remembering
the
author/authors.)

<<snipped

--

Dave Peterson


--

Dave Peterson



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
borders not aligned with cell content when printed andy g Excel Discussion (Misc queries) 2 June 5th 06 04:33 PM
can't delete a cell content in excel Mark B New Users to Excel 3 May 6th 05 06:42 PM
cell content miamac Excel Discussion (Misc queries) 0 April 18th 05 06:56 PM
Transferring cell content between workbooks using cell references Kiwi Mike Excel Discussion (Misc queries) 2 November 28th 04 12:31 AM


All times are GMT +1. The time now is 06:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"