How to find out errors in cells in a spreadsheet by a loop-program

[german12]

Hi,
I'm using Lotus-Millenium Vers.9x spreadsheet.

My worksheet has about 60 pages - and they are all combined by formulars.

Now meanwhile some pages are superfluos and I want to delete them.

As it is difficult to find out which formulars will be damaged in other pages who have
a reference to the page planned to be deleted I would like to have a small script programm
which does the following:

(example with 4 pages

a) define the name of an area e.g. A:a1...D:c:200 as "total" (script button e.g. on page D)
b) delete e.g. page C
c) go through all cells which remain in area "total" and show (or write) them somewhere
if they show "ERROR" (function @iserror...)

This would show me after each deletion of a page, whether this would cause errors
in the remaining pages. If yes - I would get back the page just deleted.

So I can imagine to clean my worksheets without big danger...
I could by this have a quick look about the consequences I just did....

Any help or similiar/better solution appreciated.

regards from germany

Klaus

 

[DaleWatson123321]

Hi Klaus,

My background in the "pre-LotusScript" programming language of Lotus 123 is significant, I don't have any experience with LotusScript.

If I were to perform your task using Lotus 123 version 5, I would do the following:

1) Create a unique range name to include all data on all sheets. I've used the name "data".
Steps:
a) Hit <Control> <Home> - takes you to cell A1 of the first sheet.
b) From the menu, use Range Name Create.
c) Type the name &quot;data&quot;.
d) Hit the <End> key
e) Hold down the <Control> key and hit the <Home> key.
f) Hit the <Enter> key.

2) Enter the following macro text into your first sheet.
/RSdata~a{ESC}ERR~FF{ESC 8}

3) Assign a range name (\A for example) to the same cell.

4) Then, using <Control> A will find the first instance of &quot;ERR&quot;.

If you prefer to see ALL the instances of ERR (one after the other), then remove the &quot;{ESC 8}&quot; from the macro text.

In your posting you refer to &quot;ERROR&quot;. If the later versions of Lotus 123 have &quot;ERROR&quot; instead of &quot;ERR&quot;, then of course you'll need to make the adjustment in the macro text.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[german12]

Dear Dale,

I have approved your macro - had some difficulties as I do use a german version of Millenium,
and found out the following:

In Version 9 of Lotus you have the possibiltiy to look directly for the contents of a cell by using
the menu for searching in cells.
So far ok - but - and that is the problem: You will find the text &quot;ERR&quot; - but not a REAL
error like 1 divided by zero - even when the search mode is switched to find labels, formulars or
numbers - the division-error above will not be found.

Any help?

Regards from Germany

Klaus