formatting rupees

[Guest]

I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas 10M
would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.

 

[Bob Phillips]

[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris

[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas 10M
would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.

 

[Bob Phillips]

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris

[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words,

whereas
10M

would normally print "10,000,000", I need it to print "100,00,000". Further,
I need every 1,000 after this to be at the three digit mark. In other words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.

 

[Guest]

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris

[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.

 

[Dave Peterson]

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came across
a negative value, which is not adhering to these formatting standards. Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:

[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break at
1,000, then the next 100, and then another 100. In other words, whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000 to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number. If
anyone can give me the custom field text for formatting, would be greatly
appreciated.

Thx.

 

[Guest]

so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:


[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.

 

[Dave Peterson]

Yes.

so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:


[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.

 

[Bob Phillips]

Yes, but you can keep adding ranges to the target range, such as

Const WS_RANGE As String = "H1:H10,M1:M5,O17"

etc.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

so this is only for a specific range, as opposed to being something I can
apply as a regular, custom format. Am I understanding correctly?
--
Boris

Bob gave instructions on how to insert the code with this:

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


And this is the line that should change to the range you want to use:

Const WS_RANGE As String = "H1:H10" '<=== change to suit

Just type the address you want in place of H1:H10.

I've not had experience with worksheet code. I guess what I'm not clear on
is how this knows what to apply this formatting to. I don't want everything
to be this formatting.
--
Boris

:

I don't think that is possible as the format cannot handle that number of
conditions (2 for positive, 2 for negative and default). What you could do
is use event code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<=== change to suit
Const POS_FORMAT As String = _

"[>=10000000]""Rs.""##\,##\,##\,##0.00;[>=100000]""Rs.""##\,##\,##0.00;""Rs.
""##,##0.00"
Const NEG_FORMAT As String = _

"[<=-10000000]""-Rs.""##\,##\,##\,##0.00;[<=-100000]""-Rs.""##\,##\,##0.00;"
"Rs.""##,##0.00"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value >= 0 Then
.NumberFormat = POS_FORMAT
Else
.NumberFormat = NEG_FORMAT
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob, thanks. I had this working the past few days, and then just came
across
a negative value, which is not adhering to these formatting standards.
Any
chance you can give me an update that would conquer that issue?

Thx.
--
Boris


:


[>=10000000]"Rs."##\,##\,##\,##0.00;[>=100000]"Rs."##\,##\,##0.00;"Rs."##,##
0.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I need to custom format a cell to read 00,00,000, instead of the usual
comma
after three spaces.

This is for an application that uses Rupees, which have a comma break
at
1,000, then the next 100, and then another 100. In other words,
whereas
10M
would normally print "10,000,000", I need it to print "100,00,000".
Further,
I need every 1,000 after this to be at the three digit mark. In other
words,
1B would need to print as "100,000,00,00,000". The key here is to get
a
comma between the uptick from 99,000 to 100,000, so that it shows up
1,00,000; and the next comma to show between the uptick from 9,999,000
to
10,000,000, where the 10M should show up as 1,00,00,000.

I'd also ideally like to have "Rs." printed right before the number.
If
anyone can give me the custom field text for formatting, would be
greatly
appreciated.

Thx.