er225711@unix.cis.pitt.edu (Martin NMN Thurn) (06/13/90)
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?
The manuals don't say jack s*** about loop variables (why give them a
name if you can't reference their value?), and there's NOT ONE WORD
about the macro debugger.
Thanks in advance to anyone who can help me with this.
--
---Martin Thurn thurn@lcl.cmu.edu
"Wow! Someone at CMU who doesn't have a bunch of
numbers and plus signs in their name!"artp@motbos.UUCP (Art Parmet) (06/16/90)
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
dougst@microsoft.UUCP (Douglas Strauss) (07/02/90)
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:!!!