Former Member
Dec 15, 2006 at 05:48 AM

# syntax for the macro - request suggestions...

29 Views

Dear All, Good morning..

I request your help in craking the below issue..

<i><b>Description:</b></i>

Calculation of a KF based on the following formula:

<b>Dlr Inv(M01/2007)= Max( ( sp.plan(m01/2007)+Dlr Inv(M12/2006) ) ; Threshold Dlr Inv(M01/2007) )- Un constrained sales( M01/2007)</b>

If you observe in the above calculation, for calculating current month Dlr Inv, we are using previous months Dlr Inv..OK.

<i><b>Requiremet:</b></i> Calculation of the above , plus, the above calculation may produce a negative number...So, when there is negative number generated in the curent month calculation, i should make it to zero, display zero and carry that zero for next months calculation...

I wrote a macro, which is doing above calculation perfectly(as it is simple 😊 )...except carrying zero to next month's calculation...As long as the above calculation is generating positive value, there is no issue at all..

<i><b>Issue:</b></i> In some cases , the above formula results a negative number, in that case, i am able to make it zero, and able to carry that zero for next calculation, if the negative is generated for the first column..problem is , if there is any negative generated in between,let us say, for June/2006, then its making it zero, but that zero is displayed for the subsequent months from there onwards, irrespective of the result is positive or negative..If i chage any number at that period for any KF, and execute macro, then it is working..Here i used auxiliary row...

I have written 3 macros with diff logic...If you want i can post them again ....

I would like to know the following things regarding macro syntax..

<b>1)Can we go for a condition after a result in the same step...?

</b>

<b>Example:

Step:(M01/2007-M12/2007)(12 iterations)

Row 1= Row2 -Row 3

IF

Row 1 < 0

Row 1 = 0

ENDIF</b>

If i execute IF condition in another step,It won't serve my purpose because, it comes to second step after completing all iterations in first step...I want , <b>IF</b> condtion to be checked after the first iteration..

In my case, macro successfully activated but no result for that condition..It is executed only up to result for the first time when i tested in Utilities--->test Macros..in the macro builder..if i execute it for the second time, then it gives result..Surprised..!!!

2) <b>Is it possible to write syntax in the following way..</b>

IF

(condition)

( Row 1 + Row 2) < 0

ResulT : Row 3=0

In my case,

It is not accepting +, - .....

Kindly suggest...

I am confident that i crack this with your mighty help..