Reading Values in a particular Format

[NimishK]

Hello


I ve used the following and getting the figure as _(5036882.00)

Textbox4.text = Format(Ws.Cells(3, 10).value, "_(*#,##0.00_);_(*#,##0.00);_(* ""-""??_);_(@_)")

How the same can be read as 50,36,882 or 50,36,882.00
if i remove the underscore from above syntax still reads as 5,036,882

thanks
NimishK

 

[dmt32]

Hi,

try

Textbox4.Text = Ws.Cells(3, 10).Text

This should display what you see in the cell in your chosen format.

Dave

 

[NimishK]

Dave

Thanks.
But if i had to put the same from Textbox to a Cell .what would it be like. How would i have given the newformat in textbox and with appropriate syntax ?

 

[dmt32]

Dave

Thanks.
But if i had to put the same from Textbox to a Cell .what would it be like. How would i have given the newformat in textbox and with appropriate syntax ?

Solution assumed that your Range is already formatted in required manner.

You can specify formatting to a range in your code by using the Range.NumberFormat property

untested example

  With Ws.Cells(3, 1)
        .Value = Me.TextBox4.Value
        .NumberFormat = "_(*#,##0.00_);_(*#,##0.00);_(* ""-""??_);_(@_)"
    End With

Dave

 

[Rick Rothstein]

How the same can be read as 50,36,882 or 50,36,882.00

See my mini-blog article here...

http://www.excelfox.com/forum/showthread.php/375-Excel-Number-Format-Indian-Style-Comma-Separation

 

[NimishK]

Thanks Dave for your help
and Rick your mini blog was an excellent refernce.
Doubts cleared.
Thanks Guys

 

[NimishK]

Got an issue for getting negative values ie. why posting on the same thread

How can i get the below syntax in Brackets ie the if the value in cell becomes negative

ws.Cells(3, 1).NumberFormat = Trim(Replace(Format(String(Len(Int(ws.Cells(3, 1))) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
ws.Cells(3, 1).NumberFormat = "General;(General)"