Display numbers in Lakhs

[Prabhu]

Hi,
>>
>> In Excel I needs to convert numbers in to indian format and the value
>> should divided by lakhs and display in Lakhs.
>>
>> Example Rs.924104600.90 should reflect as Rs.9,241(in lakhs, Indian
>> format)
Pzl find the attached workbook where i needs all the numbers in lakhs using macro.

Plz hlep.

Prabhu

[Admin]

Hi

You mean like this..

in J2 and copied down,

="Rs. "&ROUND(I2/100000,2)&" Lakhs"

[Prabhu]

Hi,

Yes, i want to devid all the numbers by(100000) but is there any way to use macro for all the numbers in the paget to convert in to lakhs.

Plz find the attached sheet whare i want to convert all the numbers value in to lakhs.

regerds,

Prabhu

[Admin]

Hi Prabhu,

Create data validation in P1 which lists two words (Absolute, In Lakhs)

This goes in the sheet module.

Right click on tab name > View code and paste this code there.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "P1" Then Exit Sub

Dim rngNumbers As Range
Dim strAddr As String

Set rngNumbers = Intersect(Me.UsedRange, Range("D:I")).SpecialCells(2, 1) 'adjust the range
strAddr = rngNumbers.Address(0, 0)

Const ConversionNum As Long = 100000

Select Case Target.Value
Case "Absolute"
rngNumbers = Evaluate("if(isnumber(" & strAddr & "),if(" & strAddr & "<>0," & strAddr & "*" & ConversionNum & "," & strAddr & ")," & strAddr & ")")
rngNumbers.NumberFormat = """Rs. ""_(* #,##0.00_);""Rs. ""_(* (#,##0.00);_(* "" - ""??_);_(@_)"
Case "In Lakhs"
rngNumbers = Evaluate("if(isnumber(" & strAddr & "),if(" & strAddr & "<>0," & strAddr & "/" & ConversionNum & "," & strAddr & ")," & strAddr & ")")
rngNumbers.NumberFormat = """Rs. ""_(* #,##0.00_)"" Lakhs"";""Rs. ""_(* (#,##0.00)"" Lakhs"";_(* ""-""??_);_(@_)"
End Select

End Sub

Change the value in P1, the code will adjust the figures what you select in P1