IF Function - Specific currency format

[Colin]

Hello,

How do I write a function that only returns a value if the cell (A1) is of
the currency Euro. I assume the If function would be used. Here is what i
want it do.

=IF(A1 is currency EURO, *0.61,"")

I'm not sure if the TYPE or CELL function is used with it or another function.

Many thanks,

 

[Gary''s Student]

First enter this tiny UDF:

Function txet(r As Range) As String
txet = ""
If r.Count = 1 Then
txet = r.Text
End If
End Function

This function returns a text string that matches the "as seen" value. This
means that if A1 contains:
€ 12.34
then =Txet(A1) will have the Euro symbol as its first character. Then we
can test that first character:


=IF(LEFT(txet(A1),1)="€","*.61","")

Have a very pleasant weekend.

 

[Bernard Liengme]

Interesting UDF. Thanks for this.
I tried it with cells formatted Currency with $, £, and ? and
LEFT(txet(A1),1) worked
But with Accounting, you need to test =MID(txet(A1),2,1)
best wishes

 

[Gary''s Student]

You are welcome. I don't often use this feature, but sometimes in VBA I use
rng.Text to get the cell contents "as displayed"

 

[Colin]

Hello,

Thank you for your response.

If I understand this correctly, I paste your UDF (What does this mean?) code
into the VBA code window of the spreadsheet that holds the information I want
to use this code with. I have not added anything else to this code in the
code window. The dropdown above the code window now shows 'general' on the
left and 'txet' in the right one.

I then pasted the function into the appropiate cell in the spreadsheet but
then Excel shows the NAME error in the cell.
The reference cell (A1) is formatted for currency with the Euro symbol.

I have not put the code into its own module or anything.

What am I doing wrong?
--
Thank you,

Colin.

You are welcome. I don't often use this feature, but sometimes in VBA I use
rng.Text to get the cell contents "as displayed"

 

[Gary''s Student]

The error message says that Excel can't find the function. This usually
means its not in the correct place:


User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the UDF in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs and macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Update this post if more problems occur...

 

[Colin]

Hello,

Many thanks for clarifying this process. Works great - many thanks.

Cheers,

Colin

 

[Harlan Grove]

If I understand this correctly, I paste your UDF (What does this
mean?) . . .

UDF is the acronym for user-defined function.

. . . code into the VBA code window of the spreadsheet that holds
the information I want to use this code with. . . . ....
I have not put the code into its own module or anything.

What am I doing wrong?

....

You need to put udfs into so-called general VBA modules, i. e., you
need to put the code into its own module.

Switch to the Visual Basic Editor (VBE) by pressing [Alt]+[F11], then
run the VBE menu command Insert > Module to create a new, blank
general module, and paste the udf code into it.

Since Excel provides many different Euro number formats, the key seems
to be the presence of the Euro symbol € somewhere in the cell's
display (.Text property). And udfs that vary with cell formatting
should be volatile so they recalc more often, so pick up formatting
changes more frequently.

Anyway, the most general way to use the proposed txet udf would be

=IF(COUNT(X99,FIND("€",txet(X99)))=2,"*0.61","")

 

[Gary''s Student]

Thank you (again) Harlan. The volatile suggestion is a good one.
--
Gary''s Student - gsnu200715

If I understand this correctly, I paste your UDF (What does this
mean?) . . .

UDF is the acronym for user-defined function.

. . . code into the VBA code window of the spreadsheet that holds
the information I want to use this code with. . . . ....
I have not put the code into its own module or anything.

What am I doing wrong?

....

You need to put udfs into so-called general VBA modules, i. e., you
need to put the code into its own module.

Switch to the Visual Basic Editor (VBE) by pressing [Alt]+[F11], then
run the VBE menu command Insert > Module to create a new, blank
general module, and paste the udf code into it.

Since Excel provides many different Euro number formats, the key seems
to be the presence of the Euro symbol € somewhere in the cell's
display (.Text property). And udfs that vary with cell formatting
should be volatile so they recalc more often, so pick up formatting
changes more frequently.

Anyway, the most general way to use the proposed txet udf would be

=IF(COUNT(X99,FIND("€",txet(X99)))=2,"*0.61","")