Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant
Dim Ndx As Integer
Dim Counter As Integer
Dim Arr As Variant
Dim Arr1 as Variant, i as Long
Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
Arr1 = Arr
i = lbound(arr)
for each cell in rng
Arr1(i) = cell.value
i = i + 1
Next
' Available quantities of the above
'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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.05, 0.01)
' Available quantities of the above
arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.001) = Arr(Ndx) And Counter + 1 <= arr1(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function


--
Regards,
Tom Ogilvy


"Max Bialystock" wrote in message
...
Here's an ingeious user defined array function written by Chip Pearson.


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.05, 0.01)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.001) = Arr(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function


It converts a dollar&cents value to the number of 100 dollar bills, 50s,
20s,
10s, 5s, 1s, 25c, 10c, 5c, and pennies.

Put a value in A1 and then select
A2:J2 and type this in:
=converttocurrency(a1) and hit control-shift-enter.



But suppose the actual coins and notes available are limited in their
quantities.

For example, suppose in the notes and coins on hand there is only one $1
bill available.

If the sum to be split up is $3.

We need a result like: $1 x 1, 25c x 8.

How can we tell converttocurrency what quantities of notes and coins are
available?