[Solved] Calc looping macro error

[Chair]

Hello all,

Sorry if this is a jumbled mess. I have been working on this for awhile and my brain is turning to mush.

I have a sheet of data that has 2 columns. The first column repeats the numbers 240-398 back to back for 431,208 rows. The second column contains signal data (all unique numerical data).

I have a macro that strips out all but the first set of 240-398 (159 rows) in my first column and cuts all but the first 159 rows in the second column and pastes the cut rows in the third (unoccupied) column. This macro is then followed by a looped macro that will run 2711 times. This macro goes to the 160th row in the 3rd column and cuts all of the remaining data and pastes it into the 4th row and so on and so forth.

The second macro runs fine until its 422 loop. Then I get a "BASIC runtime error." The error reads: "An exception occurred, Type: com.cun.ctar.uno.RuntimeException, Message: [msci_uno bridge error] UNO type of C++ exception unknown: "std.bad_alloc", RTI-name=".?AVbad_alloc@std@@"!."


As far as I can see there is nothing different about this column. And the 422 columns it created look flawless. I have no idea why it stops at this point.


Here is the Second macro:


sub Second


rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = true

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args5())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Cut", "", 0, Array())

rem ----------------------------------------------------------------------
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(1) as new com.sun.star.beans.PropertyValue
args8(0).Name = "By"
args8(0).Value = 1
args8(1).Name = "Sel"
args8(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args8())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub




And here is how I run it so it will be looped:


Sub RunSecond()
Dim i As Long, n As Long

n = 2711
for i = 1 to n
Second()
next i
End Sub



Thank you so much for your time and any input.

[Zizi64]

Please upload your real ODF type sample document here, and give us more details...
- Where the cursor is located, when you launched the macro?
- Where is (which cell, which column and row) is located the last non empty cell (the "EndOfData")?

[Chair]

Thank you for the fast response and sorry that I did not reply sooner. I have been working on the issue and have found a way around it that is sufficient for now. I have made made the loop count smaller in the sub then looped the entire sub inside of another sub with a smaller loop count to repeat the macro without it jamming.

Doing it this way it has successfully executed this macro.


Thank you for your time.