cash denominations split up

[dlindstrom]

Chip Pearson posted a very clever function a while ago:

http://groups.google.com/group/micr...ba8136d5175/679eb03c35348590#679eb03c35348590

I was wondering if there's a way for the user to input the actual
denominations available.

For example the user may have 1 x $100 note and 8 x $50 to make up
$500.

Chip's function assumes the user has 5 x $100.

Cheers,
DL

 

[Roger Govier]

Hi

The following is my rather clumsy attempt to modify Chip's function.
I set the Dollar values in Cells M2:V2 and the quantity of each on hand
in cells M1:V1
The value to be converted, I put in cell L3, and the array entered
function in cells L3:M3

Function ConvertToCurrency(ByVal Amt As Double) As Variant
Dim Ndx As Integer
Dim Counter As Integer, Counter2 As Integer
Dim Arr As Variant, Arr2 As Variant
Arr2 = Range("M1:V1") '<< Change to suit

Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
Counter2 = 0
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0: Counter2 = Counter2 + 1
While (Amt + 0.0001) >= Arr(Ndx) And Arr2(1, Counter2) > 0
Arr2(1, Counter2) = Arr2(1, Counter2) - 1
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter

Next Ndx
ConvertToCurrency = Arr
End Function

I attempted to write back the new values or the second array to cells
M1:V1 to reduce them having allowed for what had been used
in that conversion, but it just stopped the function form working (so I
have omitted the line in the above).
I don't understand why that wouldn't work, but maybe someone more
proficient with VBA will be able to provide the answer to that.

The above does appear to do what you want though, and may get you
started.

 

[vezerid]

Just my 2 cents worth...

I also tried Chip's original UDF, and it only returned in all cells the
value for the 100 denominations. I.e., for an amount of 453.68 it
returned 4 to all ten cells. Prior to testing it, I had made the
following modifications myself:

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)
Avail = Array(8, 10, 5, 24, 6, 9, 12, 7, 14, 12)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.0001) >= Arr(Ndx) And Counter <= Avail(Ndx)
Counter = Counter + 1
Avail(Ndx) = Avail(Ndx) - 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function

And this is returning 0 to all cells. I don't have much time now to
debug but I will star the thread for later, to see what is happening.
Any explanation why the original UDF did not work? I certainly
array-entered it.

Regards
Kostis Vezerides

 

[Lucas Budlong]

Kostis,

I just tested the original and it works fine.

Lucas

 

[Lucas Budlong]

The array is horizontal.

 

[vezerid]

Aha! There is something important I learned. I will now try to find out
how it should have been written so that it could be entered over a
vertical array. Thanks for the tip Lucas.

Regards,

Kostis