can you share sample file
On Tue, Jan 28, 2014 at 1:41 PM, Camukesh2015 <camukesh2...@gmail.com>wrote:
>
>
> Hi EverReady,
>
> I have downloaded a macro code for spell numbers in Indian Rupees, in
> Lakhs and Crore notation. But I am facing a problem in the code. Code is
> working fine upto 200 crore conversion into words but when I tried to
> convert above 200 crore formula showing #Value! errror.. I have tried to
> understand the problem but failed to understand problem...Kindly any body
> help to correct this mistake I am posting code here...
>
>
> Function NumbertoRupee(ByVal MyNumber, Optional incRupees As Boolean =
> True)
>
> Dim Crores, Lakhs, Rupees, Paise, Temp
>
> Dim DecimalPlace As Long, Count As Long
>
> Dim myLakhs, myCrores
>
> ReDim Place(9) As String
>
> Place(2) = " Thousand ": Place(3) = " Million "
>
> Place(4) = " Billion ": Place(5) = " Trillion "
>
> ' String representation of amount.
>
> MyNumber = Trim(Str(MyNumber))
>
> ' Position of decimal place 0 if none.
>
> DecimalPlace = InStr(MyNumber, ".")
>
> ' Convert Paise and set MyNumber to Rupees amount.
>
> If DecimalPlace > 0 Then
>
> Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
>
> MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
>
> End If
>
> myCrores = MyNumber \ 10000000
>
> myLakhs = (MyNumber - myCrores * 10000000) \ 100000
>
> MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000
>
> Count = 1
>
> Do While myCrores <> ""
>
> Temp = GetHundreds(Right(myCrores, 3))
>
> If Temp <> "" Then Crores = Temp & Place(Count) & Crores
>
> If Len(myCrores) > 3 Then
>
> myCrores = Left(myCrores, Len(myCrores) - )
>
> Else
>
> myCrores = ""
>
> End If
>
> Count = Count + 1
>
> Loop
>
> Count = 1
>
> Do While myLakhs <> ""
>
> Temp = GetHundreds(Right(myLakhs, 3))
>
> If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs
>
> If Len(myLakhs) > 3 Then
>
> myLakhs = Left(myLakhs, Len(myLakhs) - 3)
>
> Else
>
> myLakhs = ""
>
> End If
>
> Count = Count + 1
>
> Loop
>
> Count = 1
>
> Do While MyNumber <> ""
>
> Temp = GetHundreds(Right(MyNumber, 3))
>
> If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
>
> If Len(MyNumber) > 3 Then
>
> MyNumber = Left(MyNumber, Len(MyNumber) - 3)
>
> Else
>
> MyNumber = ""
>
> End If
>
> Count = Count + 1
>
> Loop
>
> Select Case Crores
>
> Case "": Crores = ""
>
> Case "One": Crores = " One Crore "
>
> Case Else: Crores = Crores & " Crores "
>
> End Select
>
> Select Case Lakhs
>
> Case "": Lakhs = ""
>
> Case "One": Lakhs = " One Lakh "
>
> Case Else: Lakhs = Lakhs & " Lakhs "
>
> End Select
>
> Select Case Rupees
>
> Case "": Rupees = "Zero "
>
> Case "One": Rupees = "One "
>
> Case Else:
>
>
>
>
>
> Rupees = Rupees
>
> End Select
>
> Select Case Paise
>
> Case "": Paise = " and Paise Zero Only "
>
> Case "One": Paise = " and Paise One Only "
>
> Case Else: Paise = " and Paise " & Paise & " Only "
>
> End Select
>
> NumbertoRupee = IIf(incRupees, "Rupees ", "") & Crores & _
>
> Lakhs & Rupees & Paise
>
> End Function
>
> ' Converts a number from 100-999 into text
>
> Function GetHundreds(ByVal MyNumber)
>
> Dim Result As String
>
> If Val(MyNumber) = 0 Then Exit Function
>
> MyNumber = Right("000" & MyNumber, 3)
>
> ' Convert the hundreds place.
>
> If Mid(MyNumber, 1, 1) <> "0" Then
>
> Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
>
> End If
>
> ' Convert the tens and ones place.
>
> If Mid(MyNumber, 2, 1) <> "0" Then
>
> Result = Result & GetTens(Mid(MyNumber, 2))
>
> Else
>
> Result = Result & GetDigit(Mid(MyNumber, 3))
>
> End If
>
> GetHundreds = Result
>
> End Function
>
> ' Converts a number from 10 to 99 into text.
>
> Function GetTens(TensText)
>
> Dim Result As String
>
> Result = "" ' Null out the temporary function value.
>
> If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
>
> Select Case Val(TensText)
>
> Case 10: Result = "Ten"
>
> Case 11: Result = "Eleven"
>
> Case 12: Result = "Twelve"
>
> Case 13: Result = "Thirteen"
>
> Case 14: Result = "Fourteen"
>
> Case 15: Result = "Fifteen"
>
> Case 16: Result = "Sixteen"
>
> Case 17: Result = "Seventeen"
>
> Case 18: Result = "Eighteen"
>
> Case 19: Result = "Nineteen"
>
> Case Else
>
> End Select
>
> Else ' If value between 20-99...
>
> Select Case Val(Left(TensText, 1))
>
> Case 2: Result = "Twenty "
>
> Case 3: Result = "Thirty "
>
> Case 4: Result = "Forty "
>
> Case 5: Result = "Fifty "
>
> Case 6: Result = "Sixty "
>
> Case 7: Result = "Seventy "
>
> Case 8: Result = "Eighty "
>
> Case 9: Result = "Ninety "
>
> Case Else
>
> End Select
>
> Result = Result & GetDigit _
>
> (Right(TensText, 1)) ' Retrieve ones place.
>
> End If
>
> GetTens = Result
>
> End Function
>
> ' Converts a number from 1 to 9 into text.
>
> Function GetDigit(digit)
>
> Select Case Val(digit)
>
> Case 1: GetDigit = "One"
>
> Case 2: GetDigit = "Two"
>
> Case 3: GetDigit = "Three"
>
> Case 4: GetDigit = "Four"
>
> Case 5: GetDigit = "Five"
>
> Case 6: GetDigit = "Six"
>
> Case 7: GetDigit = "Seven"
>
> Case 8: GetDigit = "Eight"
>
> Case 9: GetDigit = "Nine"
>
> Case Else: GetDigit = ""
>
> End Select
>
> End Function
>
>
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
*Regards*
*Ashish Koul*
*Visit*
http://www.excelvbamacros.in
Like Us on
Facebook<http://www.facebook.com/pages/Excel-VBA-Codes-Macros/151803898222297>
Join Us on Facebook <http://www.facebook.com/groups/163491717053198/>
P Before printing, think about the environment.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
