software

Chip Pearson posted a very clever function a while ago:

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,
DLHi

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(quot;M1:V1quot;) 'lt;lt; 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) gt;= Arr(Ndx) And Arr2(1, Counter2) gt; 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 Goviergt; wrote in message ups.com...
gt; Chip Pearson posted a very clever function a while ago:
gt;
gt; groups.google.com/group/micro...9eb03c35348590
gt;
gt; I was wondering if there's a way for the user to input the actual
gt; denominations available.
gt;
gt; For example the user may have 1 x $100 note and 8 x $50 to make up
gt; $500.
gt;
gt; Chip's function assumes the user has 5 x $100.
gt;
gt; Cheers,
gt; DL
gt;
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) gt;= Arr(Ndx) And Counter lt;= 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 VezeridesKostis,

I just tested the original and it works fine.

Lucasquot;vezeridquot; gt; wrote in message ups.com...
gt; Just my 2 cents worth...
gt;
gt; I also tried Chip's original UDF, and it only returned in all cells the
gt; value for the 100 denominations. I.e., for an amount of 453.68 it
gt; returned 4 to all ten cells. Prior to testing it, I had made the
gt; following modifications myself:
gt;
gt; Function ConvertToCurrency(ByVal Amt As Double) As Variant
gt; Dim Ndx As Integer
gt; Dim Counter As Integer
gt; Dim Arr As Variant
gt; Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
gt; Avail = Array(8, 10, 5, 24, 6, 9, 12, 7, 14, 12)
gt; For Ndx = LBound(Arr) To UBound(Arr)
gt; Counter = 0
gt; While (Amt 0.0001) gt;= Arr(Ndx) And Counter lt;= Avail(Ndx)
gt; Counter = Counter 1
gt; Avail(Ndx) = Avail(Ndx) - 1
gt; Amt = Amt - Arr(Ndx)
gt; Wend
gt; Arr(Ndx) = Counter
gt; Next Ndx
gt; ConvertToCurrency = Arr
gt; End Function
gt;
gt; And this is returning 0 to all cells. I don't have much time now to
gt; debug but I will star the thread for later, to see what is happening.
gt; Any explanation why the original UDF did not work? I certainly
gt; array-entered it.
gt;
gt; Regards
gt; Kostis Vezerides
gt;
The array is horizontal.quot;vezeridquot; gt; wrote in message ups.com...
gt; Just my 2 cents worth...
gt;
gt; I also tried Chip's original UDF, and it only returned in all cells the
gt; value for the 100 denominations. I.e., for an amount of 453.68 it
gt; returned 4 to all ten cells. Prior to testing it, I had made the
gt; following modifications myself:
gt;
gt; Function ConvertToCurrency(ByVal Amt As Double) As Variant
gt; Dim Ndx As Integer
gt; Dim Counter As Integer
gt; Dim Arr As Variant
gt; Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
gt; Avail = Array(8, 10, 5, 24, 6, 9, 12, 7, 14, 12)
gt; For Ndx = LBound(Arr) To UBound(Arr)
gt; Counter = 0
gt; While (Amt 0.0001) gt;= Arr(Ndx) And Counter lt;= Avail(Ndx)
gt; Counter = Counter 1
gt; Avail(Ndx) = Avail(Ndx) - 1
gt; Amt = Amt - Arr(Ndx)
gt; Wend
gt; Arr(Ndx) = Counter
gt; Next Ndx
gt; ConvertToCurrency = Arr
gt; End Function
gt;
gt; And this is returning 0 to all cells. I don't have much time now to
gt; debug but I will star the thread for later, to see what is happening.
gt; Any explanation why the original UDF did not work? I certainly
gt; array-entered it.
gt;
gt; Regards
gt; Kostis Vezerides
gt;
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