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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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,
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 |
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 |
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 |
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,
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 |
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 |
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 |
All times are GMT +1. The time now is 01:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com