Comma Seperation issues: Numeric values are completely displayed differently

[NimishK]

Hello
OMG
What i face now is
when values displayed in formula bar as 200600 it displays as 200,600 in cell instead of 2,00,600 ie incorrect comma placement
Following values
4,14,180 to display as 4,14,180 it displays as 4104,180
3,18,600 to display as 3,18,600 it dispalys as 3108,600
17676.40 displayed as 17,676 which is correct
400000 to display as 4,00,000 it dispalys as 40,00,000
i really dont know how addtional 0 is coming

For Each nmbrFrmtCell In .Range("CI3:CI50")
   nmbrFrmtCell.NumberFormat = Trim(Replace(Format(String(Len(Int(nmbrFrmtCell.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) 
Next nmbrFrmtCell

Desired is to have correct placement of comma and exact Numeric Value
when Tens, Hundreds, Thousand, TenThousand, One Lac, Ten lacs so on

Thanks NimishK

 

[sandy666]

what about completely new file?

this is your file with custom format: Custom Format

 

[NimishK]

I did that 3 times and the last file was sent on the thread. Still Nothing happens. Am in Soup

 

[sandy666]

How about the file I sent in the post#11 ?

 

[NimishK]

Yes your file was great. but then i tried copying the column from your file to my file. Still did not work. Thanks for the efforts you have taken
This kind of thing am experiancing for first time. Never has happend like this before

By the way is there any customFormat which will include 0. like post #1

Will await the VBA part of it.

 

[sandy666]

this is from Macro Recorder (and it works!):

Sub Macro1()
 Columns("I:I").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""-""_);_(@_)"
End Sub

maybe it will help

 

[NimishK]

Also am trying to recollect that this data yesterday was on excel 2010 copied the file in Excel2013 but with following code

With ws
    Set r = Union(.Columns("D"), .Columns("E"))
End With


With r.FormatConditions
   
    .Delete
   
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=1000").NumberFormat = "0.00" '
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=100000").NumberFormat = "0\,000.00"
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=10000000").NumberFormat = "0\,00\,000.00"
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=1000000000").NumberFormat = "0\,00\,00\,000.00"
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=100000000000").NumberFormat = "0\,00\,00\,00\,000.00"
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=10000000000000").NumberFormat = "0\,00\,00\,00\,00\,000.00"
End with

Is it because of the above code

 

[sandy666]

sorry mate but I really can't help
there is many masters of vba who can help
all what I can say is: good luck and have a nice day

 

[NimishK]

sorry mate but I really can't help
there is many masters of vba who can help
all what I can say is: good luck and have a nice day

Thanks anyways. Will await the technical reply