what's wrong with this number format

[vipulgos]

I have tried this code earlier also. But this time it gives error message. pl. guide me to reinstate the same.

Selection.NumberFormat = _
"[>=10000000]Rs. #,##,##,##0;[>=1000000<9999999]Rs. ##,##,##0;[>=100000<999999]Rs. #,##,##0;[>=10000<99999]Rs. ##,##0;Rs. #,##0"

This is for indian ruppees conversion.
I can't remind what's going wrong with this

 

[xenou]

This should work:

Selection.NumberFormat = Chr(34) & "Rs. " & Chr(34) & "#,##0"

Though we haven't handled the case for negative numbers or zero here.

-----
Note: It is probably the decimal point that is the problem in your original format - this is parsed by Excel as a part of the number to be formatted

 

[vipulgos]

Dear xenou,
It's not working.
Only because of the fact that:
My Indian rupees format works as under:
for 1000 it may be written as 1,000
for 10000 it may be written as 10,000
For 100000 it may be written as 1,00,000
For 1000000 it may be written as 10,00,000
For 10000000 it may be written as 1,00,00,000
and so on

 

[R Pelle]

Dear xenou,
It's not working.
Only because of the fact that:
My Indian rupees format works as under:
for 1000 it may be written as 1,000
for 10000 it may be written as 10,000
For 100000 it may be written as 1,00,000
For 1000000 it may be written as 10,00,000
For 10000000 it may be written as 1,00,00,000
and so on

Hi, I don't have a solution for you but I'm just curious to find out why you are using a 'strange' format. Is this typical for India?
I would expect to see
1,000
10,000
100,000
1,000,000
10,000,000
etc...

Rob

 

[vipulgos]

Though it is strange,
the fact is very much against your expected format
Yes, we have names for that. You may have name like thousand, million, billion etc. We have thousand, Lac, ten lacs, Crore etc.

 

[xenou]

Does this help:
http://www.excelbanter.com/showthread.php?t=1288

 

[vipulgos]

YES,
Accidentally I also found my old data from my DVD and the found the exactly same and now it's worked

 

[shg]

Another way. Select the range of interest and run ApplyLakh:

Sub ApplyLakh()
    FondOLakh Selection
End Sub
 
Sub FondOLakh(r As Range)
    ' shg 2009-0607
    ' Applies Lakh/Crore formatting to numeric cells in r
        '   One                               1
        '   Ten                              10
        '   Hundred                         100
        '   Thousand                      1,000
        '   Lakh                       1,00,000
        '   Crore                   1,00,00,000
        '   Thousand Crore      1,000,00,00,000
        '   Lakh Crore       1,00,000,00,00,000
 
    Const dEps  As Double = 0.001
    Const LN10 As Double = 2.30258509299405
    Static vFmt As Variant
    Dim cell    As Range
    Dim i       As Long
    Dim dLog    As Double
 
    If IsEmpty(vFmt) Then
        vFmt = Array( _
                              "0.00_);(0.00)", _
                             "00.00_);(00.00)", _
                            "000.00_);(000.00)", _
                         "0\,000.00_);(0\,000.00)", _
                        "00\,000.00_);(00\,000.00)", _
                     "0\,00\,000.00_);(0\,00\,000.00)", _
                    "00\,00\,000.00_);(00\,00\,000.00)", _
                 "0\,00\,00\,000.00_);(0\,00\,00\,000.00)", _
                "00\,00\,00\,000.00_);(00\,00\,00\,000.00)", _
               "000\,00\,00\,000.00_);(000\,00\,00\,000.00)", _
            "0\,000\,00\,00\,000.00_);(0\,000\,00\,00\,000.00)", _
           "00\,000\,00\,00\,000.00_);(00\,000\,00\,00\,000.00)", _
       "#0\,00\,000\,00\,00\,000.00_);(#0\,00\,000\,00\,00\,000.00)")
    End If
 
    On Error GoTo Oops
    Application.EnableEvents = False
 
    For Each cell In Intersect(r, r.Worksheet.UsedRange)
        If VarType(cell.Value2) = vbDouble Then
            If cell.Value2 = 0# Then
                cell.NumberFormat = "-??_)"
            Else
                i = Int(Log(Abs(cell.Value2 + dEps)) / LN10)
                If i < 0 Then i = 0
                If i > UBound(vFmt) Then i = UBound(vFmt)
                cell.NumberFormat = vFmt(i)
            End If
        End If
    Next cell
Oops:
    Application.EnableEvents = True
End Sub

 

[R Pelle]

We have thousand, Lac, ten lacs, Crore etc.

Ah! thanks for that, appreciated!
Rob

 

[Rick Rothstein]

Another way is to have the cell's format adapt automatically as needed using VB event code. The following method handles both positive and negative values up to 15 significant digits (Excel's limitation on numeric entries)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Range, Cell As Range
  Set R = Range("C:C")
  On Error Resume Next
  If Not Intersect(Target, R) Is Nothing Then
    With Target
      If WorksheetFunction.IsNumber(.Value) Then
        .NumberFormat = Trim(Replace(Format(String(Len(Int(Abs(.Value))) - 1, "#"), _
        " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
      Else
        .NumberFormat = "General"
      End If
    End With
  ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
    For Each Cell In Target.Dependents
      If Not Intersect(Cell, R) Is Nothing Then
        With Cell
          If .Value <> "" Then
            If WorksheetFunction.IsNumber(.Value) Then
              .NumberFormat = Trim(Replace(Format(String(Len(Int(Abs(.Value))) - 1, "#"), _
              " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
            Else
              .NumberFormat = "General"
            End If
          End If
        End With
      End If
    Next
  End If
End Sub

Note 1: This is event code and must be installed in the worksheet module for the worksheet that will have this functionality. If you are unfamiliar with how to do this, the easiest way is to right click the name tab at the bottom of the worksheet, select View Code from the popup menu that appears and then copy/paste the above code into the code window that opened up.

Note 2: You must set the range on the worksheet that you want this event code to be active for. You do that in the Set statement at the top of the code... just change the Range("C:C"), which applies the code only to Column C, to whatever range of cells you want the code to apply to.

Note 3: This event code automatically handles numeric constants (numbers directly typed into a cell) or numbers that result from formula calculations (provided the formula does not reference cells on other worksheets).