ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Need help with format number (https://www.excelbanter.com/excel-discussion-misc-queries/235470-need-help-format-number.html)

agpaco

Need help with format number
 
In 2003, I need a way to: if de number don't have ‚¬, he put ZAR. But if have
‚¬, he don´t modify. I think the only way is creating a new number format, but
I don't no how.

jamescox[_2_]

Need help with format number
 

Take a look at your post - you may be using a character set that this
site can't handle - something is causing some of the characters you are
using to be runreadable as rendered.

At any rate, I don't think number formatting can deal with what you
want to do - but I could be wrong.

If this is for display to the user, you might be able use an If
statement to detect the presence of whatever you are looking for and
return the appropriate value.

Good luck on this!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111533


agpaco

Need help with format number
 
Thank for your help. I first think to use If, but how can detect the euro
format?

"jamescox" wrote:


Take a look at your post - you may be using a character set that this
site can't handle - something is causing some of the characters you are
using to be runreadable as rendered.

At any rate, I don't think number formatting can deal with what you
want to do - but I could be wrong.

If this is for display to the user, you might be able use an If
statement to detect the presence of whatever you are looking for and
return the appropriate value.

Good luck on this!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111533



jamescox[_9_]

Need help with format number
 

There may be better ways, and this requires you to add a code module and
a user-defined function to your workbook.

Add a code module (if you don't already have one) and put this code in
it:

Public Function HasEuro(rCell) As Boolean

Dim sAddress As String

sAddress = rCell.Address

If InStr(ActiveSheet.Range(sAddress).NumberFormat, ChrW(8364)) 0
Then
HasEuro = True
Else
HasEuro = False
End If

End Function



Use this cell formula, where B4 is the cell which may or may not have
the Euro sumbol in it:

=IF(HasEuro(B4),"",B4)

In the code above, the 8364 in ChrW(8364) is the Unicode value of the
Euro symbol for my English version of Excel - yours might be different
and you would need to find out what the value is and put it there. You
may be able to enter the following code in the Immediate Window of the
VBA IDE

?activesheet.range("d26").numberformat

where you replace d26 with some cell that does contain the euro, and it
will return something like

[$€-2] #,##0.00

then, type the line below in the Immediate window:

?ascw("")

copy the Euro symbol and paste it between the two "" (with no extra
blank spaces), then press Enter. For me, it gave

8364

Whatever number you get when you press Enter is the value you should
use in the HasEuro code - but if it's not a 4 digit number, something
has gone wrong.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111533



All times are GMT +1. The time now is 12:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com