🚀 go-pugleaf

In article <24904@unix.cis.pitt.edu>, er225711@unix.cis.pitt.edu (Martin NMN Thurn) writes:
> I need help with (what I thought was going to be) a simple Excel macro.
> The part that doesn't work is: I have a =FOR loop, and I want to use the
> counter variable to reference rows/columns of a worksheet (active or macro,
> just so I can put my data _somewhere_!)  In other words, given (e.g.)
> =FOR("mycounter", 4, 18, 1)
> how can I use the value of "mycounter" in an "R1C1"-type expression within 
> the loop?  
>
>.....[ comments deleted ]

The solution to your problem is a feature of Excel called "Text Concatenation".
You are correct that it is not described in the manuals. However, it is
described in detail in a Microsoft Application Note by the same title.
(PS: The hot line support from Microsoft is responsive, courteous and quite
knowledgable !!!)

In short, concatenation allows you to use the ampersand (&) to join two or more
text values together to form a new text value.

For example, let's assume that you have a variable used as a counter which was
defined using:

=SET.NAME("My_Counter",1)

Assume the active cell points to some starting location of values.  To then use
"My_Counter" as an offset value:

=SELECT("R["&My_Counter&"]C[0]")

Concatenation can be useful for other imaginative things such as printing out
the values of variables in INPUT, MESSAGE and ALERT statements:

=MESSAGE(TRUE,"The Current Value of My_Counter is: "&My_Counter&"")

Another trick for formatting your interactive statements:

=SET.NAME("CR",CHAR(13))	ASCII Carriage Return - The MAC NEWLINE char

Then:

=ALERT("Warning- Current Value For My_Counter:"&CR&""&My_Counter&"",2)

Would result in an ALERT box (assume My_Counter has the value of 1234):

Warning- Current Value For My_Counter:
1234

Hope this helps.......

-- 
----
Art Parmet @ Motorola Semiconductor, Marlboro, Ma.
UUCP: {decvax, gatech!mcdchg}..!motbos!artp   Phone: +1 508-481-8100   

In article <669@motbos.UUCP> artp@motbos.UUCP (Art Parmet) writes:
>In article <24904@unix.cis.pitt.edu>, er225711@unix.cis.pitt.edu (Martin NMN Thurn) writes:
>> The part that doesn't work is: I have a =FOR loop, and I want to use the
>> counter variable to reference rows/columns of a worksheet (active or macro,
>
>The solution to your problem is a feature of Excel called "Text Concatenation".
>(PS: The hot line support from Microsoft is responsive, courteous and quite
>knowledgable !!!)
>
>=SELECT("R["&My_Counter&"]C[0]")

Or you may want to use the Offset macro command.  Although it is in A1 
notation but much easier to read.

=SET.NAME("My_Counter",1)
=SELECT(OFFSET(ACTIVE.CELL(),My_Counter,0))

Doug

P.S.  Thanks for the PS:!!!