Convert/format numbers in Lakhs and Crores

[rishicool786]

Hi All,

I am a newbie with <acronym title="visual basic for applications">VBA, I have a requirement in my project
Client wants to convert numbers in cells to form of Lakhs and Crores(Indian Numbering format) with comma , sepeator after every 2 digits. How can i do this through VBA, since I have to this if User inputs Indian format then.

Hope this clears my requirement, Please let me know for any clarification.

Below I have tried,
'Event called after report refresh
Function AFTER_REFRESH()
Dim EPMObj As New FPMXLClient.EPMAddInAutomation
Dim rngData As Range
'Gets the range of the report data
Set rngData = Range(EPMObj.GetDataTopLeftCell(ActiveSheet, "000") & ":" & EPMObj.GetDataBottomRightCell(ActiveSheet, "000"))
'Calls the formatting procedure
Call procFormatCell(rngData)
End Function
'Formatting procedure
Public Sub procFormatCell(rngData As Range)
Dim rngCell
Dim LIST
Dim TEST
Dim Worksheet1
Set TEST = ActiveWorkbook.Worksheets
Set Worksheet1 = TEST("Selection")
'Selection is the name of the worksheet where the user selects the option from drop down list
LIST = Worksheet1.Cells(15, 10)
' Cells (15,10) is the cell reference where the selection is done, (row,column). Do not give a namespace to this cell
For Each rngCell In rngData
Select Case LIST
Case Is = "ABSOLUTE"
rngCell.Value = rngCell.Value
Case Is = "CRORE"
rngCell.Value = rngCell.Value / 10 ^ 7
rngCell.NumberFormat = "#,##0.00"
Case Is = "LACS"
rngCell.Value = rngCell.Value / 10 ^ 5
rngCell.NumberFormat = "#,##0.00"
Case Else
rngCell.Value = rngCell.Value
End Select
Next rngCell
End Sub

Now when I run above code, It gives error 'Type Mismatch' at this statement rngCell.Value = rngCell.Value / 10 ^ 7

Help Please.
</acronym>

 

[yky]

You can use the number format #","00000 to display number in lakhs. Using that number format, 98372940 is displayed as 983,72940.

 

[yky]

If you want to display 99999999.99 as 999,99,999.99, use ###","##","###.00.