Adding macro in a @if 1

[tgrog]

I am trying to write a @if that if a cell is <= another cell then calc that cell into a value in lieu of a formula and move to the next cell down. This would check a column of formulas and those that meet the criteria would be changed to values. Any Ideas?????

@if(b7<=$a$4,{macro????},&quot; &quot

The Macro would be something like '{edit}{calc}~~{d} copied to each row.

 

[DaleWatson123321]

tgrog,

Based on your description, here are the steps to follow:

1) Create a range name for the first cell in your column of formulas. In my example, I've used the name &quot;start&quot;.

2) Create a range name for cell $A$4. I've used the name &quot;amt&quot; (short for amount).

3) Off to the side, or preferably on a separate sheet that you've designated as your &quot;macro&quot; sheet, create a range name for the start of your macro. I've used the name: \Q.

By using such a range name (starting with &quot;\&quot;, followed by a letter, I expect you're aware that to activate this macro, you hold down the <Control> key and hit the letter - &quot;Q&quot;.

4) In this cell you've named \Q, enter the following:
{GOTO}start~{check}

5) Leave a blank cell, and in the next cell after the blank cell, create the range name &quot;check&quot;, and enter the following:
{IF @CELLPOINTER(&quot;contents&quot=&quot;&quot;}{GOTO}start~{QUIT}

6) In the next cell, enter:
{IF @CELLPOINTER(&quot;contents&quot<=amt}/RV~~

7) In the next cell, enter:
{D}{BRANCH check}

You should now be able to activate your macro, and it will do as you've requested... convert to values all those formulas that contain values <= the value in $A$4 (the cell you've named &quot;amt&quot.

If, by chance your list of formulas is LONG, you should be aware of the code for turning the windows and panel OFF, as this will make this process MUCH faster.

To turn the windows/panel OFF, use the following in the cell you've named \Q:
{INDICATE &quot;Processing... please wait&quot;}{WINDOWSOFF}{PANELOFF}

To turn the windows/panel back ON, use the following in the cell you've named &quot;check&quot;:
{IF @CELLPOINTER(&quot;contents&quot=&quot;&quot;}{WINDOWSON}{PANELON}{INDICATE}{GOTO}start~{QUIT}

Please note that the above routine will terminate when it reaches a BLANK cell, so it's assumed your scenario is one where there are NO BLANK cells within your range of formulas to be checked.

I hope this helps. Please advise as to how you make out.

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

 

[german12]

Hi Dale,

even to the fact that I did not ask for that problem, I would like to give you a big star
for that excellent clear answer.

Regards from Germany

Klaus Peace worldwide - it starts here...

 

[DaleWatson123321]

Klaus,

&quot;Thanks&quot; for appreciating

Regards from Canada ;-)

...Dale