| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
note/coinage converter???
On Wednesday, June 7, 2000 3:00:00 AM UTC-4, Chip Pearson wrote:
Sheila, Here's a simple VBA function that will return an array of 10 items. The elements of the array indicate the number of bills and or coins to make the input amount. It returns the counts for $100 bills, $50, $20, $10, $5, $1, quarters, dimes, nickels, and pennies, so the array has 10 elements. To use it in a worksheet, select a range of 10 cells, e.g., D2:M2, type =ConvertToCurrency(A2) and press Ctrl+Shift+Enter. Because the function returns an array, you *must* press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. Function ConvertToCurrency(ByVal Amt As Double) As Variant Dim Ndx As Integer Dim Counter As Integer Dim Arr As Variant Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.5, 0.01) For Ndx = LBound(Arr) To UBound(Arr) Counter = 0 While (Amt + 0.0001) = Arr(Ndx) Counter = Counter + 1 Amt = Amt - Arr(Ndx) Wend Arr(Ndx) = Counter Next Ndx ConvertToCurrency = Arr End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting Services www.cpearson.com Hi Chip, I've found this VBA function to be very helpful despite my being totally new to macros. However, my currency also includes a $2 note and issuing of the 1 cent coin has been discontinued. How can the VBA function be modified to adapt to these changes? Thanks much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Like a note but it's not-What is it? | Excel Discussion (Misc queries) | |||
| coinage | Excel Discussion (Misc queries) | |||
| Notes and Coinage for Wages | Excel Worksheet Functions | |||
| Validation note to comment note | Excel Discussion (Misc queries) | |||
| Note to Bob Phillips | Excel Programming | |||
Linear Mode
