Want to display numbers in Indian Currency format

[sarvagya]

A1 = 18,56,57,576.00
A2= 18,39,01,547.00

A3=(+)1756029

In cell A3,formula applied is

Please Login or Register  to view this content.

IN cell A1 and A2, I displayed the numbers in Indian currency format using the Visual Basic Macros

Please Login or Register  to view this content.

In cell A3, I also want to run this Macro, but Error is coming because it contains(-) or (+) before it.

How to do that , I want cell A3 contains(-) OR (+) and also displayed numbers in Indian Currency after Subtraction of A1-A2.

Help me!!!

[jason.b75]

You can't apply a number format to text, which is what your formula is producing.

Change the formula to =A1-A2

And try this code

Please Login or Register  to view this content.

[sarvagya]

Thanks jason for reply....but using your Macros IndianNumberFormat-->My number A1 = 18,56,57,576.00 is displayed as simply 185657576.00

[jason.b75]

Is A1 a number or the result of a formula?

It works fine for me with 185657576.00 entered as a number.

If it is a formula then it should still show with the (+) / (-) prefix, so I'm guessing that your numbers are actually text.

See if this fixes it, if not then you will need to attach your workbook so I can see where it is going wrong.

Please Login or Register  to view this content.

[Beamernsw]

I think sarvagya is happy with the way his macro works for formatting the numbers in A1 and A2. It's just A3 that he wants different. He wants (+)/(-) in front of the existing format from the macro...but only for that cell.

If there is something that "marks" those cells...like borderlines or a cell beside it like "Balance" or something that can be checked to find the cells that you want the different format applied to, then maybe you could use that as a condition to add the (+)/(-) into your existing macro. Maybe make the text a slightly different colour and the macro could detect that and apply the different format.

Sorry if I misunderstood or overstepped

[jason.b75]

I think you're on the right track beamer, from the original post, I read it as the cell where (+)/(-) is required contains a formula, where the others contain numbers.

I did originally post a slight edit (as below) to sarvagya's code, but the urge to shrink it got the better of me

Please Login or Register  to view this content.

This still needs the formula in A3 to be changed to =A1-A2

Another method would be to stick with the original code, and use this formula in A3

=IF(A1<A2,"(-)","(+)")&TEXT(ABS(A1-A2),REPT("##\,",MAX((LEN(ABS(A1-A2))-3)/2,0)+0.5)&"##0.00")

But if the code that I provided isn't working then it implies that the source data is in a non-numeric format rather than the code is broken, so based on that theory, the original code shouldn't work either.

[sarvagya]

Please Login or Register  to view this content.

This code is abosolutely working......
Thanks Very much again...

I just want to ask one simple question regarding macros...Suppose i apply the macros on the cells and run it....Now I closed the file and do not save the macro...I just want to ask, when I open the file next time..on that cells, macros code will be applied or not??

[jason.b75]

You don't save the macro, you save the workbook, which will save any changes made by the macro.

If you close without saving then any changes made, by you, or by the macro since the last save will be discarded.

If you save as a macro free workbook (.xlsx) then any changes made by the macro up to that point will be saved.