Google| Subject | Re: spreadsheet ergonomics |
| From | DFS |
| Date | 04/06/2017 16:46 (04/06/2017 10:46) |
| Message-ID | <oc5k56$ar5$2@dont-email.me> |
| Client | |
| Newsgroups | comp.os.linux.advocacy |
| Follows | owl |
| Followups | owl (3h & 2m) |
On 4/6/2017 8:38 AM, owl wrote:
owlWith small code changes I can stop it at a specific count, or at a cell location, or at a time.
DFS <nospam@dfs.com>wrote:DFSowl
On 4/5/2017 10:01 PM, DFS wrote:ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"Whoops. Make that "=SUM(R[-9]C:R[-1]C)"
http://imgur.com/a/NHHS7
Is there not a way to feed it a count instead of just seeing how many it can create in 15 seconds?
I put the 15 second stop in there because it takes a long time to run.
Extrapolating with the current code, it'll create right at 20,700 tables per minute, and the full sheet would hold about 1.5M, so 72 minutes to fill it up.
I doubt Excel 2003 will let you add 1.5M named ranges. And the sheet would be monstrous because of the formulas. Maybe I could turn autocalc off and it would be quicker.
Edit: updated it to stop at user-defined points. ----------------------------------------------------------------------- Public Sub createtables()
Const MAXROWS = 65535 Const MAXCOLS = 256
Dim startTime As Date, endTime As Date Dim row As Long, col As Integer Dim tblcnt As Long, tablesize As Byte
Dim stopper As String Dim stopTime As Integer, stopTable As Long Dim stopRow As Long, stopCol As Integer
stopper = "Time" stopTime = 10 stopTable = 2257 stopRow = 2000 stopCol = 2
row = 1 col = 1 tblCount = 1 tblSize = 11
startTime = Timer For col = 1 To MAXCOLS For row = 1 To (MAXROWS - tblSize) Step tblSize
Cells(row, col).Select ActiveCell.FormulaR1C1 = "Table" & tblCount ActiveWorkbook.Names.Add Name:="Table" & tblCount, _ RefersToR1C1:=Chr(col + 64) & row & ":" & Chr(col + 64) & (row + tblSize)
Cells(row + tblSize - 1, col).Select ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
If stopper = "Table" Then If tblCount >= stopTable Then GoTo finish ElseIf stopper = "Cell" Then If (row >= stopRow And col >= stopCol) Then GoTo finish ElseIf stopper = "Time" Then If (Timer - startTime) >stopTime Then GoTo finish End If
tblCount = tblCount + 1
Next row Next col
finish: endTime = Timer Debug.Print "Stopped by " & stopper & ": created " & tblCount & " tables in " & Format(endTime - startTime, "0.0") & " seconds"
End Sub -----------------------------------------------------------------------
Stopped by Table: created 2257 tables in 13.1 seconds Stopped by Cell: created 12097 tables in 55.5 seconds Stopped by Time: created 1713 tables in 10.0 seconds -----------------------------------------------------------------------
It ran fine but suddenly quit working and now throws the famous 'Code execution has been interrupted'. Fails on ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
If you have Excel 2003 see if it works for you.
03/31/2017-
-
-
