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

[Max Bialystock]

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?

 

[Tom Ogilvy]

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

 

[Max Bialystock]

Thanks Tom.

Is it possible to populate arr1 from a range in Sheet1?

Max