Forum Home
Press F1
Thread ID: 27387	2002-11-21 07:38:00	Excel and filenames again!	Mike (15)	Press F1

Post ID	Timestamp	Content	User
100243	2003-12-16 07:48:00	Hi Parry, The active sheet is the one with the file names in it. What was happening was because I'd attached the macro to the sheet rather than the workbook - now that I've attached it to the workbook it seems to work... mostly :) On a few records it works fine. But when I try it on a full set of records (60000+) it only returns the first row (set to the number of columns I originally asked for). Thanks for trying though :) It's not too important that I get this working, as I could just use Russell's idea of pasting however many thousand on seperate sheets :D Mike.	Mike (15)
100244	2003-12-16 08:41:00	Hi Mike, sorry I see I made a couple of mistakes with the code. The major failure being to account for 65536 row being occupied so hopefully this will work better. I placed sample data in column A from row 1 to 65536 which is the row limit and it worked OK. Took about 14 secs which isnt too bad considering the amount of data. Result using 9 columns is 7282 rows or 270 printed sheets if you want to kill a small Brazilian forest :-) Sub ZigZag() 'By Parry 16/12/03 Dim Rng As Range, MyArray() As String, Arr1 Dim NumCols As Integer, NumRows As Long, NumFiles As Long Dim R As Long, C As Integer, J As Long, Result As Range Dim Sh As Worksheet On Error GoTo ErrHand 'Check range If Range("A1").Value = "" Then MsgBox "You must have a value in cell A1!" Exit Sub End If 'Set range holding file names If Range("a65536") <> "" Then 'Account for 65536 rows Set Rng = Range("A1:A65536") Else Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row) End If 'Find # rows & columns wide for the print NumCols = Application.InputBox(prompt:="Enter # columns", _ Title:="Width of Print Job", Type:=3) NumRows = Application.InputBox(prompt:="Enter # rows", _ Title:="Height of Print Job", Type:=3) 'Determine array dimensions NumFiles = Rng.Cells.Count If NumFiles Mod NumCols = 0 Then Arr1 = NumFiles / NumCols Else Arr1 = Int(NumFiles / NumCols) + 1 'Duh forgot to kill decimals End If ReDim MyArray(1 To Arr1, 1 To NumCols) For R = 1 To Arr1 For C = 1 To NumCols J = J + 1 MyArray(R, C) = Cells(J, 1).Value If J = 65536 Then Exit For 'stop if at 65536 row Next C Next R 'Add new Sheet to store the result On Error Resume Next Set Sh = Worksheets("ZigZag") If Error Then Application.DisplayAlerts = False Sheets("ZigZag").Delete Application.DisplayAlerts = True Err.Clear End If Sheets.Add Sheets(1).Name = "ZigZag" Set Result = Sheets("ZigZag").Range(Cells(1, 1), Cells(Arr1, NumCols)) Result = MyArray Exit Sub ErrHand: MsgBox "Error # " & Err.Number & vbLf & Err.Description Exit Sub End Sub	parry (27)
100245	2003-12-16 08:59:00	Well done Parry, it worked :) Now I've crashed Excel though :p nevermind, I'll rerun it and then NOT convert everything back to numbers :D Just a question - is it possible to go down the page, and then across? EG I want to to print something like 55 lines per page, so it goes from 1 to 55, then column b 1 to 55, across the 9 columns, then jumps to A56 and continues... and so on? Or is that a lot harder to do? I really want to learn this VBA type stuff now! :D Mike.	Mike (15)
100246	2003-12-16 09:57:00	Yep most things are possible :-) Im not sure I exactly get what you mean. Reading the data from your sheet is easier to do in long lines - so reading all down column A then all down column b is easier than reading 55 rows in column A, then 55 rows in Column B then the next 55 in column A etc. Or do you mean how many columns in the ZigZag sheet? The issue isnt really looping through it but trying to understand what can go wrong because of the limitations of the number of rows in Excel. A database is far easier for something like this because there are no row limits like you strike in Excel but youve already mentioned thats not an option.	parry (27)
100247	2003-12-16 10:05:00	How many file names do you have and how may characters does the largest file name have, and what font size do you want the to print-out to have.	Russell D (18)
100248	2003-12-16 19:26:00	> Yep most things are possible :-) > Im not sure I exactly get what you mean. Reading the > data from your sheet is easier to do in long lines - > so reading all down column A then all down column b > is easier than reading 55 rows in column A, then 55 > rows in Column B then the next 55 in column A etc. Or > do you mean how many columns in the ZigZag sheet? I mean that its easier to read columns down then across on the printed page. So going by roughly 55 rows per page, I want to be able to read down the first column, then down the next etc. then after the 9 or so columns I turn the page and I start again reading down the columns. Does that make more sense now? > The issue isnt really looping through it but trying > to understand what can go wrong because of the > limitations of the number of rows in Excel. > A database is far easier for something like this > because there are no row limits like you strike in > Excel but youve already mentioned thats not an > option. There wouldn't be any more rows in Excel - I'd just like to be able to read down the page rather than left to right; but still page by page :D Am I asking too much? Mike.	Mike (15)
100249	2003-12-16 19:28:00	> How many file names do you have and how may That's unknown at this stage... they're not filenames this time, they're just numbers - non-sequential numbers too some of the time. > characters does the largest file name have, and what probably 7 digits. > font size do you want the to print-out to have. Just the standard excel setup - I think I get about 55 rows and 9 columns per a4 page... plus/minus some maybe. Mike.	Mike (15)
100250	2003-12-16 21:36:00	> I mean that its easier to read columns down then > across on the printed page. So going by roughly 55 > rows per page, I want to be able to read down the > first column, then down the next etc. then after the > 9 or so columns I turn the page and I start again > reading down the columns. Does that make more sense > now? Click, yes I understand. :D I would need to think about the best way to do this because of the speed issue. My previous code used arrays which are far quicker at writing the information to a sheet than from one range to another. Arrays by nature read left to right then down so writing to a new sheet as per my previous code fits in nicely. Your scenario is a bit more complicated so I need to understand how to get this into an array properly because you can get some erratic results when it comes time to writing the data from the array if the values have not been place in the array in the correct order. I'll have a look at it but I wont spend too much time on it at this stage as Russell seems to have some ideas and may have a cleaner solution for you. As an aside you sound as though your manually checking the file names against something. Im not sure what your doing but couldnt this be automated?	parry (27)
100251	2003-12-16 23:55:00	The following macro will take the data in column A and align it sequentially 55 rows down and 9 columns across, then repeat below at row 56 for the next 55 rows and 9 columns across and repeat until all the data is arranged. If this sheet is then printed, the data on the pages will be sequential as you have requested. Edit the "pl" and "pc" values to suit your page row and column sizes. HTH Sub align_data() Dim n, m, a, b, c, pl, pc As Integer Dim rng As Range 'pl = lines per page pl = 55 'pc = columns per page pc = 9 m = Range("a1", Range("a1").End(xlDown)).Cells.Count / (pc * pl) 'c = number of entries c = Range("a1", Range("a1").End(xlDown)).Cells.Count 'p = number of pages p = Application.WorksheetFunction.RoundUp(m, 0) b = 0 For b = 0 To p Range("a1").Offset(pl * b, 0).Select n = -1 For a = 1 To pc - 1 n = n + 1 ActiveCell.Offset(pl, 0).Select Set rng = Range(ActiveCell, ActiveCell.End(xlDown)) rng.Select If rng.Cells.Count > c Then Exit Sub End If rng.Cut Range("a1").Offset(pl * b, n + 1) Range("a1").Offset(pl * b, n + 1).Select Next a ActiveCell.Offset(pl, 0).Select Set rng = Range(ActiveCell, ActiveCell.End(xlDown)) rng.Select rng.Cut Range("a1").Offset(pl * (b + 1), 0) Next b End Sub	Russell D (18)
100252	2003-12-17 05:12:00	Well done Russell it worked for me. :-D I hope you dont mind me mentioning this but in your declarations you have ... Dim n, m, a, b, c, pl, pc As Integer then later... p = Application.WorksheetFunction.RoundUp(m, 0) Are you wanting n,m,a,b,c & pl to be Integers? They are actually variants unless you declare a data type explicitly, so will include decimals when you divide etc. You may already know but if you declare multiple variables on the same line in VBA then they all require a data type assigned explicitly with the default being Vaiant if you havent assigned a type. If you wanted Integers you wuld have to do this (a pain I know)... Dim n as Integer, m as Integer, a as Integer etc etc... Apologies if Im out of line but thought I should mention it. Cheers :-)	parry (27)
1 2 3 4 5