Re: Macro not working

Jeff wrote:
> It check to see A2 in the active page contains "NSF", if so the
macro puts 
> active page name on page "D" in column "A", starting in
row 8. Macro is 
> supposed to quit when a page with blank A2 is encountered.

BTW, you can get this result without using a macro.  Put the following formula 
in D:A8, then copy down as far as you wish.  Beware wordwrap:

@IF(@INDEX($E..$IV:$A$2,0,0,@ROWS($B$8..$B8)-1)="NSF",@INDEXTOLETTER(@
ROWS($B$8..$B8)+3),"")

Cheers,
Uli

Wow! A lot has happened since I've been out.

I am sorry to say I never got any of Roy's 16 lines of code (I would love to 
see it, if it saved some where)

The {ReCalc CalcBlock}did solve the problem.

Thanks for your help!



I am now considering Uli's formula as the most elegant way out, although I 
don't exactly get how it works.

@IF(@INDEX($E..$IV:$A$2,0,0,@ROWS($B$8..$B8)-1)="NSF",@INDEXTOLETTER(@
ROWS($B$8..$B8)+3),"")

Can it display the actual name page. i.e. "Leona_Job" as opposed to
"E" and 
can it handle an additional condition (see below, omitted before to make 
macro easier to communicate about)?



DoNSF         {Let 
@CONCATENATE("D:A",@@("SFJobRow")),@PROPERTY("Active_Pa
ge.Name")}

                     {Let 
@CONCATENATE("D:B",@@("SFJobRow")),@SUM(@@(SFWage))}





SFWage           @CONCATENATE(SFPage,":C2..",EndColumn,"2")





Jeff

"Uli" <nojunkmail@spamless.com> wrote in message
news:479cea72_1@cnews...
>
> Jeff wrote:
>> It check to see A2 in the active page contains "NSF", if so
the macro 
>> puts active page name on page "D" in column "A",
starting in row 8. Macro 
>> is supposed to quit when a page with blank A2 is encountered.
>
> BTW, you can get this result without using a macro.  Put the following 
> formula in D:A8, then copy down as far as you wish.  Beware wordwrap:
>
>
@IF(@INDEX($E..$IV:$A$2,0,0,@ROWS($B$8..$B8)-1)="NSF",@INDEXTOLETTER(@
ROWS($B$8..$B8)+3),"")
>
> Cheers,
> Uli
> 

Jeff wrote:
>
@IF(@INDEX($E..$IV:$A$2,0,0,@ROWS($B$8..$B8)-1)="NSF",@INDEXTOLETTER(@
ROWS($B$8..$B8)+3),"")
> 
> Can it display the actual name page. i.e. "Leona_Job" as opposed
to "E"

Thusly:

@IF(@INDEX($Epage..$IV:$A$2,0,0,@ROWS($A$7..$A7)-1)="NSF",@PAGENAME(@R
OWS($A$7..$A7)+3),"")

Note:  Keep in mind, page names are tricky; if the target page does NOT have a 
custom name, the @PAGENAME function returns an ERR.  So, within the context of 
your scenario, all pages with NSF in A2 MUST have a custom name in order for 
this to work.

> can it handle an additional condition (see below, omitted before to make 
> macro easier to communicate about)?
> 
>                      {Let 
> @CONCATENATE("D:B",@@("SFJobRow")),@SUM(@@(SFWage))}
> 
> SFWage          
@CONCATENATE(SFPage,":C2..",EndColumn,"2")

I don't see why not, but instead of me pulling a guess out of a hat, you need to

explain a little better exactly how the range boundaries of the @SUM formula are

obtained, i.e., EndColumn is not defined (does it vary, and what is it anyway: 
a column identifier, a cell address?), and exactly what is the role of the 
terminal "2" appended to EndColumn?  In short, by what algorithm are
the @SUM 
formula boundaries determined?  This will affect the structure of the formula.

Uli

Uli:



@IF(@INDEX($Epage..$IV:$A$2,0,0,@ROWS($A$7..$A7)-1)="NSF",@PAGENAME(@R
OWS($A$7..$A7)+3),"")



Worked great as soon as I figured out that ($Epage is not a page in my file.



"you need to explain a little better exactly how the range boundaries of
the 
@SUM formula are obtained, i.e.,

EndColumn is not defined (does it vary, and what is it anyway:
a column identifier, a cell address?), and exactly what is the role of the
terminal "2" appended to EndColumn?  In short, by what algorithm are
the 
@SUM
formula boundaries determined?  This will affect the structure of the 
formula"



Oopps! In a hurry I pulled out a wrong additional condition.

What I meant to say was:



{IF @YEAR(@@(YearDate))+1900=Year}



YearDate           @CONCATENATE(SFPage,":A6")



Year                 User inputs year (i.e 2007) in to D:A2 (named Year)



Jeff

"Uli" <nojunkmail@spamless.com> wrote in message
news:479eae61_1@cnews...
>
> Jeff wrote:
>>
@IF(@INDEX($E..$IV:$A$2,0,0,@ROWS($B$8..$B8)-1)="NSF",@INDEXTOLETTER(@
ROWS($B$8..$B8)+3),"")
>>
>> Can it display the actual name page. i.e. "Leona_Job" as
opposed to "E"
>
> Thusly:
>
>
@IF(@INDEX($Epage..$IV:$A$2,0,0,@ROWS($A$7..$A7)-1)="NSF",@PAGENAME(@R
OWS($A$7..$A7)+3),"")
>
> Note:  Keep in mind, page names are tricky; if the target page does NOT 
> have a custom name, the @PAGENAME function returns an ERR.  So, within the

> context of your scenario, all pages with NSF in A2 MUST have a custom name

> in order for this to work.
>
>> can it handle an additional condition (see below, omitted before to
make 
>> macro easier to communicate about)?
>>
>>                      {Let 
>>
@CONCATENATE("D:B",@@("SFJobRow")),@SUM(@@(SFWage))}
>>
>> SFWage          
@CONCATENATE(SFPage,":C2..",EndColumn,"2")
>
> I don't see why not, but instead of me pulling a guess out of a hat, you 
> need to explain a little better exactly how the range boundaries of the 
> @SUM formula are obtained, i.e., EndColumn is not defined (does it vary, 
> and what is it anyway: a column identifier, a cell address?), and exactly 
> what is the role of the terminal "2" appended to EndColumn?  In
short, by 
> what algorithm are the @SUM formula boundaries determined?  This will 
> affect the structure of the formula.
>
> Uli
> 

Roy:



> FWIW: http://www.lemoto.myby.co.uk/temp/tt1.zip
Thanks. It is fun for me to see how other people do this, particularly if 
they are better at it.

Is there any place on the web that has good macro samples with explanations?

QP Help leaves me wanting more. Am I turning in to a geek?


Jeff
"lemoto" <ctech@corel.ca> wrote in message 
news:VA.00004a93.006e367a@corel.ca...
>
> Jeff:
>> I am sorry to say I never got any of Roy's 16 lines of code (I would
love 
>> to
>> see it, if it is saved somewhere)
>
> FWIW: http://www.lemoto.myby.co.uk/temp/tt1.zip
>
> A few notes are included.
>
>> Can it display the actual name page. i.e. "Leona_Job" as
opposed to "E"
> Yes (now that I have read Uli's notes <s>).
> -- 
> Good wishes!
> Roy Lewis
> C_Tech volunteer
> (UK)
>
>