Category : Lotus and other Spreadsheets
Archive   : LAST15.ZIP
Filename : @LAST.DOC

Output of file : @LAST.DOC contained in archive : LAST15.ZIP

@ L A S T

Search & Replace Functions for Lotus 1-2-3

@LAST is a Lotus 1-2-3 add-in that provides 1-2-3 release 2 and 2.01
users with the ability to find specific cell entries and/or to modify those

@LAST can be called into action by the user, or by macros within 1-2-3.

@LAST is designed to behave in precisely the same way as 1-2-3 does.
Any 1-2-3 user will be immediately familiar with the way @LAST
operates. Its functions are intuitive and immensely useful.

@LAST is fast and has extremely modest memory requirements.

(C) Copyright 1987 by
Alan Wassyng Consulting Ltd
20 Darby Way
Thornhill, Ontario L3T 5V1
Telephone: (416) 731-4053

This product is not manufactured, approved or supported by
Lotus Development Corporation.


This software is protected by copyright. You may make copies of the
software for the purpose of retaining a backup copy.

You may also copy the program to share with friends who wish to try it,
as long as:

The program is distributed in unmodified form, complete with
documentation, installation programs, and example worksheet.

No fee, charge or other consideration is requested or accepted.

The program is not distributed in conjunction with any other product.


Alan Wassyng Consulting Ltd., specializes in the effective use of PC's.
We develop quality software, especially in the fields of science /
engineering and higher education. We also design and run superb computer
training courses for corporations.


If you intend to use @LAST on a regular basis, please register and show
your support for the author.

Commercial, business or governmental use by non-registered users is

To register, send the form below and a cheque for the applicable amount,

Alan Wassyng Consulting Ltd.
20 Darby Way,
Thornhill, Ontario L3T 5V1

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _


Name: ______________________________________________________

Company: ______________________________________________________

Address: ______________________________________________________

City: __________________ State/Prov: ___________________

Zip/Post Code: _____________ Country: ___________________

Phone: ________________________________

Type of Registration: _____________ Amount: ______________

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Registration options:

Single User: You receive the latest version of @LAST on 5.25 inch
diskette, together with printed documentation. The cost of this is
$28 US or $35 Cdn.

Site: This allows unlimited copies to be used at a single
geographical (city-wide) site. 10 copies of the software and printed
documentation are provided. The cost of this is $475 US or $595 Cdn.


Alan Wassyng Consulting Ltd warrants any physical diskette and physical
documentation, if enclosed, to be free of defects in materials and
workmanship for a period of 60 days from the date of purchase. In the
event of notification of defects in materials and/or workmanship within
the warranty period, Alan Wassyng Consulting Ltd will replace the defective
diskette or documentation. Alan Wassyng Consulting Ltd's entire liability
and your sole and exclusive remedy shall be the replacement of the
defective diskette and/or documentation.

Alan Wassyng Consulting Ltd makes no other warranty of any kind, either
expressed or implied, including but not limited to implied warranties of
merchantability and fitness for a particular purpose. In no event will
Alan Wassyng Consulting Ltd be liable for direct, indirect, special, or
consequential damages including but not limited to any loss of
business, interruption of service, loss of anticipatory profits or any
other commercial damage.


Throughout this manual the following trademarks are used. Lotus and
1-2-3 are the registered trademarks of Lotus Development Corporation.
IBM is the registered trademark of International Business Machines
Corporation. @LAST is the trademark of Alan Wassyng Consulting Ltd.



1.1 Requirements . . . . . . . . . . . . . . . . . . . . . . 1
1.2 Installation . . . . . . . . . . . . . . . . . . . . . . 1
1.2.1 Running 1-2-3 off a hard disk . . . . . . . . . 1
1.2.2 Running 1-2-3 off a floppy diskette . . . . . . 2
1.3 Invoking @LAST . . . . . . . . . . . . . . . . . . . . . 2

2.1 The @LAST Menus . . . . . . . . . . . . . . . . . . . . . 4
2.2 Functional Descriptions of Menu Items . . . . . . . . . . 5
2.2.1 Find . . . . . . . . . . . . . . . . . . . . . 5
2.2.2 Modify . . . . . . . . . . . . . . . . . . . . 6
2.2.3 Range . . . . . . . . . . . . . . . . . . . . . 8
2.2.4 Controlling Parameters . . . . . . . . . . . . 8 Global . . . . . . . . . . . . . . . . . . 9 Direction . . . . . . . . . . . . . . . . 9 Case . . . . . . . . . . . . . . . . . . . 10
2.2.5 Update . . . . . . . . . . . . . . . . . . . . 10
2.2.5 Status . . . . . . . . . . . . . . . . . . . . 10
2.2.6 Quit . . . . . . . . . . . . . . . . . . . . . 10

3.1 Example Worksheet . . . . . . . . . . . . . . . . . . . . 11
3.2 Examples . . . . . . . . . . . . . . . . . . . . . . . . 11
3.2.1 Finding Errors in Formulas . . . . . . . . . . 11
3.2.2 Changing Values . . . . . . . . . . . . . . . . 11
3.2.3 Alignment of Labels . . . . . . . . . . . . . . 12
3.2.4 Changing Direction . . . . . . . . . . . . . . 12
3.2.5 Using Wildcards . . . . . . . . . . . . . . . . 12
3.2.6 Finding Formula Errors Caused by Modifications 12
3.2.7 Specifying Case Sensitive Searches . . . . . . 13
3.2.8 Invoking @LAST from 1-2-3 Macros . . . . . . . 13

@LAST Users' Guide November 1987


1.1 Requirements

@LAST requires Release 2 or 2.01 of Lotus 1-2-3. It can be run on
any IBM PC/XT/AT PS/2 or compatible computer running under DOS 2.0
or later. It can be run from either a hard or a floppy disk.
@LAST will use about 20K bytes of memory.

1.2 Installation

Make a backup copy of the @LAST diskette and store the original in
a safe place. From this point on, whenever the documentation
refers to the @LAST diskette use the copy rather than the original.

All that is required to use @LAST, is that the file called @LAST.ADN
should be copied onto the disk you are using for 1-2-3. In addition,
if this is the first Lotus 1-2-3 add-in that you are installing, you
will have to modify the file called 123.SET that is already stored
on your system disk. The program ADD_MGR.EXE is used to include the
add-in driver in 123.SET. This can be achieved by typing ADD_MGR
123.SET with both ADN_MGR.DRV and ADD_MGR.EXE in the same
directory as 123.SET.

The above procedures have been automated so that all you have to
do is type in a single command.

1.2.1 Running 1-2-3 off a hard disk

Assume that the directory you have stored the 1-2-3 files in,
is called \123 on the C drive. Make certain that the default
drive you are using is the C drive, and that the current
directory is \123. To do this type C: (where
means press the carriage return or Enter key). Then
type cd \123. Now place the @LAST diskette in drive
A, and type A:HARD.


@LAST Users' Guide November 1987

1.2.2 Running 1-2-3 off a floppy diskette

If you are installing @LAST on a floppy diskette, there is
not enough space on the floppy to include add-ins. Thus, the
1-2-3 help file has to be moved from the system diskette to
another diskette. Run 1-2-3 and make the help facility
"removable". To do this, run 1-2-3 and then select
/Worksheet Global Default Other Help Removable.
(See the 1-2-3 Reference Manual for details.)
Now copy 123.HLP from the 1-2-3 diskette to the @LAST
diskette. Then erase 123.HLP from the 1-2-3 diskette.

The installation procedure has been automated as much as
possible. After making the help facility removable, and with
the DOS system diskette that you are using in drive A, place
the @LAST diskette in drive B. Type B:FLOPPY.

(Note that the file 123.DYN on the PRINTGRAPH diskette must be
available in the 1-2-3 directory, or on the 1-2-3 system diskette.
The floppy installation procedure will copy it to the 1-2-3 system
diskette. Hard disk users must make certain it is in the correct

At this stage @LAST is installed and ready to be used.

1. If you change any of the hardware peripherals you use with
1-2-3 and use the 1-2-3 Install program to install the device
(display, printer, etc.), then you must again change 123.SET
to enable the use of add-ins. To do this simply type ADD_MGR
123.SET while in the 1-2-3 directory.

2. If you want to remove the add-in manager from 123.SET then
type DEL_MGR 123.SET while in the 1-2-3 directory.

1.3 Invoking @LAST

In order to use @LAST you must already be using 1-2-3, i.e. @LAST
runs only from within 1-2-3. You first need to attach the add-in
to 1-2-3, and then "invoke" the add-in. All this means is that after
the add-in (in our case, @LAST) is invoked, it is available for use
inside 1-2-3.

To attach @LAST, press - (press the key and while
holding it down, press the function key, ).

This will produce a typical 1-2-3 menu as follows.
[Attach] Detach Invoke Clear Setup Quit
Load add-in application into memory
[] indicates the highlight bar


@LAST Users' Guide November 1987

Select to "Attach" an add-in by pressing or by pressing
while the word Attach is highlighted. (The highlight can be moved
using the arrow keys just as in 1-2-3.)

At this stage the names of all the available add-ins stored on your
disk will appear. Highlight @LAST and press .

You will now be prompted to assign a function key (which is used
together with the key) to call @LAST whenever you want it.
Your choices are function keys , and . You can also
elect not to use a key, but this is not recommended. Let us assume
that you want to use - to call @LAST anytime you require
it while working in 1-2-3. Press <9> to indicate your choice,
press to clear the introductory screen, and then press
to return to 1-2-3.

You have now attached @LAST to 1-2-3, and it can be called up by
simply pressing the function key you chose together with the
key. Thus, in our example, - will invoke @LAST at any
time within 1-2-3.

1. Add-ins can only be invoked while you are in the "READY" mode
within 1-2-3. You cannot call @LAST while you are in a 1-2-3
command menu, for example.

2. To detach @LAST, press - and select for Detach,
and then for Quit.

3. Add-ins can be attached and/or invoked through macros. Two
example macros are included in a sample worksheet referenced
in section 3.2.8.

4. You can use the Setup option in the above menu system to
automatically attach a maximum of 8 add-ins each time you
start 1-2-3. One of these can also be automatically invoked.


@LAST Users' Guide November 1987


@LAST provides you with the capability to find cells which contain
specific contents. You are able to specify the contents and the type of
cell you are searching for. The search is performed inside a 1-2-3
range. You can use range names to specify the search range, or you can
"point" to the range in typical 1-2-3 fashion. In addition, you can
elect to modify the content of the cells that match your criteria. The
following sections demonstrate the use of @LAST, and explain the
options available to you.

2.1 The @LAST Menus

Upon calling up @LAST from within 1-2-3 (see section 1.3) the
following menu will appear.

[Find] Modify Range Global Direction Case Update Status Quit
Find cell(s) in range which match specified contents

This menu works in precisely the same way as do normal 1-2-3
menus. You can select any item by pressing the first character in
the item (it is always capitalised), or by using the arrow keys to
move the highlight bar and pressing to select the
highlighted item.

As in normal 1-2-3 menus, the key will take you back one
level in the menu structure. also clears existing user
input when you are prompted for cell contents to either find or

There are four major items on this menu. They are "Find",
"Modify", "Range" and "Quit". The other items mainly involve
options for directing your search.

The function of each of the four major items should be obvious, but
it is vital to note that any find or modify request will take place
only within the bounds of the currently set range.

Once the range is set, it is remembered until it is changed, or until
a new worksheet is started, or until @LAST is detached from 1-2-3.
When you invoke @LAST, the range is automatically set to be the
entire active worksheet area. You can reset the range at any time.


@LAST Users' Guide November 1987

2.2 Functional Descriptions of Menu Items

2.2.1 Find

This option begins a search for a cell whose contents match
the contents you specify. Upon selecting Find, the following
menu will appear.

[Formula] Label Value Error
Find cell containing a formula

This allows you to specify the type of cell to search for. If
you wish to search for a formula (this includes date functions,
etc.), select Formula. If you wish to search for a character
string, select Label (1-2-3 refers to character strings as
labels). If you wish to search for a number, either integer
or real, then select Value. The final choice, Error, will
search for formula cells which have resulted in an error (ERR
is displayed on the worksheet).

For all cases other than Error, you will be prompted to specify
the contents of the cell you are searching for, as below.

Find value cell(s) containing: _

Labels and formulas are restricted to 167 characters, and
numbers to 80 characters.

If the cell contains a number (you selected Value), then you
must specify the complete number. If the cell concerned
contains a label or formula, then you can specify just a portion
of the label or formula. Cells are considered to be successfully
matched if:

a) In the case of value cells, the number specified for the
match is identical to that contained in the cell.

b) In the case of a formula or label, the cell holds an entry
which contains (or is identical to) the character string
specified for the match.

Character strings are allowed to contain a wildcard. A
wildcard is simply a character which will match any
character(s) in the cell contents. Two such wildcards
are allowed. The first wildcard is the tilde ~. This
character will match any number of characters in the cell
contents. The other wildcard is the question mark ?. This


@LAST Users' Guide November 1987

wildcard can be used to match a single character in the
cell contents.

Note: You can use only one wildcard in any match


1. If you specify a match criterion of s~g, then this
will successfully match cells which contain string,
sailing, song, soggy, etc.

2. If you specify a match criterion of s?g, then this
will successfully match a cell containing soggy, but
not string, or sailing, or song.

If a match is found in a hidden cell (1-2-3 allows you to hide
entire columns), then an informative message is displayed in
the control panel. The message will give the address (cell
location) in which the matching contents have been found.

2.2.2 Modify

Modify can be thought of as "Find & Modify". The first part
of this is precisely the same as for Find (Error is not an
option, however). The second part is to specify what to
change the matched cell contents to. It is quite feasible that
you will want to change the value 68 inside a cell, into the
label Total. Similarly, you may want to change the formula
@DATE(68,10,18) to @DATE(87,10,18).

Thus you will be prompted to specify the type of cell you want
the new cell to be (the menu selection will look very much like
that in the Find menu).

After this you will be prompted to specify the new contents,
as follows.

Modified value cell to contain: _

Labels and formulas are again restricted to 167 characters, and
numbers to 80 characters.

In the event of a successful match, the new contents of the
cell will be displayed in the 1-2-3 control panel, and you
will be asked to verify that the replacement should proceed.


@LAST Users' Guide November 1987

If you reply (upper or lower case) or press , the
replacement will be performed. If you press (upper or
lower case), or , the original contents will not be
modified. These are the only acceptable responses.

If the matched cell is in a hidden column, then the address
of the cell is displayed in the control panel.

If the matched cell is protected and global protection is
enabled, then a warning message is added to the verify request.


1 Find (value) 68 and modify to (value) 456.
This will result in the first cell in the range (the search
progresses across rows) that contains the integer 68 to
be changed so that it now contains the integer 456.

2 Find (value) 68 and modify to (label) ABC.
This will result in the first cell in the range that
contains the integer 68 to be changed so that it now
contains the label 'ABC. (1-2-3 automatically places the
quote ahead of the string, unless the first character in
the string is " or ^.)

3 Find (label) b and modify to (label) xyz.
This will result in the first cell in the range that
contains the character b (upper or lower case) to be
changed so that the b becomes xyz. Thus, if cell E53
contains the label 'ABC before the modify, it will contain
the label 'AxyzC after the modify.

4 Find (label) s~g and modify to (label) PR~K.
This will result in the first cell in the range that
contains the string s..g (upper or lower case) to be
changed so that the s--g becomes PR--K. Thus, if cell
D45 contains the label 'songs before the modify, it will
contain the label 'PRonKs after the modify.

5 Find (label) s?g and modify to (label) P?D.
This will result in the first cell in the range that
contains the string s.g (upper or lower case) to be
changed so that the s-g becomes P-D. Thus, if cell B4
contains the label 'soggy before the modify, it will
contain the label 'PoDGy after the modify.

6 Find (formula) sin~+ and modify to (formula) cos~-. This
will convert a formula cell that contains SIN(1.234/D5)+E6
to COS(1.234/D5)-E6.


@LAST Users' Guide November 1987

2.2.3 Range

This option enables you to set the range of cells through which
@LAST will search for matching cells.

After choosing Range, the following prompt appears.

Range in which to Find/Modify: E5

(The E5 here is just an example. The actual contents will depend
on the position of the 1-2-3 cell-pointer.)

At this stage you can specify the range by typing in a range
name (it must have been already set in the worksheet you are
working in), or you can type in the range coordinates (e.g.
A2..L500), or you can "point" to the range. In short, you can
specify the range just as you would if you were specifying a
range in 1-2-3 itself.

2.2.4 Controlling Parameters

In all the above examples we made specific assumptions.

1) We assumed that the search starts at the top row in the
range, progresses across the columns, and then down the
rows. This will be referred to as a Forward search.

2) We assumed that just the first match is found, and if a
modify is requested just that one is performed.

3) We assumed that we are always asked to confirm the
modification before it is actually performed.

4) We assumed that characters in character strings can be
upper or lower case when testing for a successful match.

These are, in fact, default settings. However, we are at liberty
to change these settings if we require.

The direction of the search can be set to be either Forward
or Backward (start in last row, right-most column, progress
leftwards across columns, and then up to the preceding row,

We can request that all matches (and modifications) be found.
This is referred to as a Global Find and/or Modify.


@LAST Users' Guide November 1987

If we request a Global modify, then we can further choose to
Verify the modifications before they are made. Alternatively,
we can choose No-verify.

In terms of character matches, we can choose to have the
match successful if the cases of the characters match (Case
Sensitive), or accept a match independent of the case of the
characters (Not Sensitive).

All of these conditions can be set through menu choices as
shown below. Global

In normal operation just the first matched cell will be
found (and modified if requested). However, by
selecting Global, you can specify that all matching cells
should be found (and modified if necessary). After
selecting Global, the following menu appears.

[Verify] No-verify Cancel
Find/Modify all matches and confirm replacements

If you select Verify it is an indication that you wish to
confirm each modification before it is performed.

If you select No-verify, then all modifications will be
made without waiting for confirmation.
(In the case of a Find, rather than a Modify, both Verify
and No-verify are treated in the same way. The 1-2-3
cell pointer will be placed in the matched cell, and the
program will wait until you press a key before it
continues to the next matched cell.)

If you select Cancel, then you will return to "single"
mode, where only the first matched cell is dealt with.

Note: At the end of a successful global operation, you
are automatically put back into "single" mode. Direction

This option simply lets you choose whether the search will
progress Forward through the range, or Backward through
the range. Whatever is selected remains in force until
a new worksheet is started, or until @LAST is detached
from 1-2-3.


@LAST Users' Guide November 1987 Case

This option lets you choose whether a label search is case
sensitive, i.e. upper case alphabetic characters can
only be matched by the identical upper case character,
etc. The menu choices are Not and Sensitive. The
default mode is not sensitive. If you choose to make
matches case sensitive, then in the case of labels
(formulas are always not case sensitive), e does NOT
match E, for example.

2.2.5 Update

Choosing this option simply recalculates the entire worksheet.

2.2.5 Status

This option displays the current settings for range, verify,
direction of search, and whether or not the search is to be
case sensitive.

2.2.6 Quit

Choosing Quit at this level will place you back in 1-2-3. Many
of the settings for @LAST will remain in memory (range,
direction, etc.), and @LAST can be called up again at any stage
by pressing the Alt key combination you selected. If you
detach @LAST, or exit 1-2-3, then you simply have to invoke
@LAST again before you can use it (see section 1.3).


@LAST Users' Guide November 1987


3.1 Example Worksheet

A sample worksheet called @LASTEX.WK1 is included on your @LAST
diskette. This worksheet contains some simple instances of how
@LAST's capabilities can help you create and examine worksheets

3.2 Examples

The following examples are to be tried from within the worksheet
@LASTEX.WK1. Entries to be typed are shown in bold face. Keys to
be pressed are shown inside less-than and greater-than pairs.
means press the Enter key, for example.

3.2.1 Finding Errors in Formulas

In many cases formulas result in errors that can affect many
of your results. Obvious indications of a problem are those
when 1-2-3 displays "ERR" to indicate an illegal operation.
The cell containing the formula may not be visible on the
screen, since it may lie in a portion of the worksheet that
is not being displayed.

In order to test for (all) formula errors, press for
Global, (you don't care whether Verify or No),
for Find, and then for Error.

At every error, the program will move the cell pointer to the
cell containing the error, and wait for you to press any key
before continuing to the next one. If there are no errors in
the current range, a beep will sound, and a message saying
"NO MATCH FOUND" will appear at the bottom of the screen.

3.2.2 Changing Values

There is a cell in the worksheet which contains the number
100. To change this number to 110, type
for Modify, for Value (type of cell to find), and
This specifies that you wish to find the first integer cell in
the range containing 100.
Now type for Value (modified type) and 110.


@LAST Users' Guide November 1987

This specifies that you want to change the matched cell's
contents to the integer 110.

There are a few cells containing the integer 30. To change
all of these to the number 29.95, type for Global,
for Verify, for Modify, for Value, and 30.
Then type for Value, and 29.95. (Type the
in those cases in which you have to erase any values
typed previously, as they are still remembered.)

3.2.3 Alignment of Labels

Throughout the worksheet, the total results of specific
columns are calculated, and the word Total is shown in the
cell to the left of the cell. The label Total has been left
aligned in its cell. If we wish to right align these labels
(but not all the other labels in the range), we can type
for Global, for No Verify (we know we want to change
all of them), for Modify, for Label, and 'total.
Then type for Label, and "Total.

3.2.4 Changing Direction

If we now decide that we want to change the LAST occurrence
of Total in the worksheet, to read FINAL TOTAL, we proceed as
Type for Direction, for Backward, for Modify,
for Label and Total. Then type for Label, and

3.2.5 Using Wildcards

There are cells containing the formula @SUM(sub-range) in one
column of the worksheet. We now decide we wish to modify the
formulas so that they become @AVG(sub-range)*20. Clearly the
sub-range will be different in each cell (typically obtained
from a relative copy). To make this change, type
for Global, for No Verify, for Modify, for
Formula, and then @sum(~). Now type for Formula,
and @avg(~)*20.

3.2.6 Finding Formula Errors Caused by Modifications

In some cases, you will modify a formula so that the new
formula contains an error. In these cases 1-2-3 will convert
the formula into a label, and will insert the characters ERR
at the beginning of the label. To find such cells simply
Find a Label which contains 'ERR, e.g. 'ERR.


@LAST Users' Guide November 1987

3.2.7 Specifying Case Sensitive Searches

If you wish to search for a label containing TOTAL, but do not
want to pick-up labels containing Total, you can proceed as
Type for Case, for Sensitive, for Find (we are
still going Backwards remember), for Label, and

3.2.8 Invoking @LAST from 1-2-3 Macros

To invoke @LAST from within a macro, use
{APP4}[email protected]~. Where {APP4} replaces -, I is for
Invoke, @LAST is the name of the add-in, and ~ is the usual
macro symbol for . To specify @LAST wildcards within
a Lotus macro, use {~} and {?}.

To return to 1-2-3 from @LAST, include {ESC 2} in your macro.

A simple macro to locate a formula cell containing @DATE(..)
and to modify it to @DATE(88,10,18) is included in the sample
worksheet. (Examine it carefully to see how to use to
clear existing formula/label/value entries safely.)

Quit @LAST and press - to execute the macro. (The
macro is located in cell P2.)

Note: The above assumes that @LAST is already attached. A
macro to attach @LAST is also easy to create. The following
macro attaches @LAST and assigns function key to invoke
@LAST. Afterwards @LAST can be invoked by the user pressing
or by the macro sequence above.

{APP4}[email protected]~9{ESC}Q
(This macro is included in @LASTEX.WK1 in cell P9.)