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