Dear Barnie,
I have the functions according to your advice but it returns #VALUE!, please
explain on how should I go through these steps to solve my problem. FYI, it
doesn't work for both USD and AFA format.

Thanks,

"Bernie Deitrick" wrote:

Daoud ,

Try changing this line in the function:

SPELLDOLLARS = Trim(Temp) & " Dollars and " & Cents

to this:

SPELLDOLLARS = Trim(Temp) & IIf(InStr(1, myCell.NumberFormat, "$#") 0, " Dollars and ", " AFA and
")
& Cents

The part $# is from the numberformat for the standard dollar formatting - your formatting may
differ, so to find a usable stringf, format your cell for $, then run this macro:

Sub test()
MsgBox ActiveCell.NumberFormat
End Sub


Note the format string that is returned. Then format for AFA and run it again. Pick out a unique
combination of characters that appears in the $ format and not the AFA format, and insert it in
place of the $# (which may work anyway).

Note that reformatting the cell will not cause the SPELLDOLLARS function to recalc, so you may need
to force a recalc.

HTH,
Bernie
MS Excel MVP


"Daoud Fakhry" wrote in message
...
Dear Michael,
Thanks for your reply, but I use 2 currencies at the same time. Let's assume
I have my digits in A1 and I want to return the word in A2. If I change the
currency of A1 to $ the word should be changed to Dollars and if I change the
currency to AFA it should return me AFA. Please let me know if you get my
point.

Regards,
Daoud Fakhry

"Michael M" wrote:

Hi Daoud
This will do the trick.
I believe it came from one of JE McGimpseys books, but my apologies to the
author, if it didn't.
After placing the code in your sheet, all you have to do is use the formula
=SPELLDOLLARS(A1) If your data is in A1

Regards
Michael M

Function SPELLDOLLARS(cell) As Variant
' Spelldollars Macro
' Macro recorded 24/12/2004
Dim Dollars As String
Dim Cents As String
Dim TextLen As Integer
Dim Temp As String
Dim Pos As Integer
Dim iHundreds As Integer
Dim iTens As Integer
Dim iOnes As Integer
Dim Units(2 To 5) As String
Dim bHit As Boolean
Dim Ones As Variant
Dim Teens As Variant
Dim Tens As Variant
Dim NegFlag As Boolean

' Is it a non-number?
If Not IsNumeric(cell) Then
SPELLDOLLARS = "This is not a numeric value, please try again!!"
'CVErr(xlErrValue)
Exit Function
End If

' Is it negative?
If cell < 0 Then
NegFlag = True
cell = Abs(cell)
End If

Dollars = Format(cell, "###0.00")
TextLen = Len(Dollars) - 3

' Is it too large?
If TextLen 15 Then
SPELLDOLLARS = "This number is too large to print, please try again"
'CVErr(xlErrNum)
Exit Function
End If

' Do the cents part
Cents = Right(Dollars, 2) & " cents"
If cell < 1 Then
SPELLDOLLARS = Cents
Exit Function
End If

Dollars = Left(Dollars, TextLen)

Ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven",
"Eight", "Nine")
Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen",
"Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty",
"Seventy", "Eighty", "Ninety")

Units(2) = " Thousand, "
Units(3) = " Million, "
Units(4) = " Billion, "
Units(5) = " Trillion, "

Temp = ""

For Pos = 15 To 3 Step -3
If TextLen = Pos - 2 Then
bHit = False
If TextLen = Pos Then
iHundreds = Asc(Mid$(Dollars, TextLen - Pos + 1, 1)) - 48
If iHundreds 0 Then
Temp = Temp & "" & Ones(iHundreds) & " Hundred and"
bHit = True
End If
End If
iTens = 0
iOnes = 0

If TextLen = Pos - 1 Then
iTens = Asc(Mid$(Dollars, TextLen - Pos + 2, 1)) - 48
End If

If TextLen = Pos - 2 Then
iOnes = Asc(Mid$(Dollars, TextLen - Pos + 3, 1)) - 48
End If

If iTens = 1 Then
Temp = Temp & " " & Teens(iOnes)
bHit = True
Else
If iTens = 2 Then
Temp = Temp & " " & Tens(iTens)
bHit = True
End If
If iOnes 0 Then
If iTens = 2 Then
Temp = Temp & "-"
Else
Temp = Temp & " "
End If
Temp = Temp & Ones(iOnes)
bHit = True
End If
End If
If bHit And Pos 3 Then
Temp = Temp & "" & Units(Pos \ 3)
End If
End If
Next Pos

SPELLDOLLARS = Trim(Temp) & " Dollars and " & Cents
If NegFlag Then SPELLDOLLARS = "(" & SPELLDOLLARS & ")"

End Function



"Daoud Fakhry" wrote:

Hi masters,
I would like to use spellnumber in my worksheet. I want that if I put $200
using currency format in one cell it should give me Two Hundred US Dollars
and No Cents and if I put AFA 200 using currency format it should be able to
return Two Hundred AFA in another cell. I have found the following link to
put in VBA code but some time this is also not working.
http://www.microsoft.com/office/comm...xp=&sloc=en-us

Is there any one who can help me, thanks.

Daoud Fakhry