cash denominations split up
 

Chip Pearson posted a very clever function a while ago:

http://groups.google.com/group/micro...9eb03c35348590

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

cash denominations split up
 

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.

--
Regards

Roger Govier


wrote in message
ups.com...
Chip Pearson posted a very clever function a while ago:

http://groups.google.com/group/micro...9eb03c35348590

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

cash denominations split up
 

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

cash denominations split up
 

Kostis,

I just tested the original and it works fine.

Lucas


"vezerid" wrote in message
ups.com...
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

cash denominations split up
 

The array is horizontal.


"vezerid" wrote in message
ups.com...
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

cash denominations split up
 

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