# cash denominations split up

1. ## cash denominations split up

Chip Pearson posted a very clever function a while ago:

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

2. ## Re: 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

<dlindstrom@bigpond.com> wrote in message
> Chip Pearson posted a very clever function a while ago:
>
>
> 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
>

3. ## Re: 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

4. ## Re: cash denominations split up

Kostis,

I just tested the original and it works fine.

Lucas

"vezerid" <vezerid@act.edu> wrote in message
> 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
>

5. ## Re: cash denominations split up

The array is horizontal.

"vezerid" <vezerid@act.edu> wrote in message
> 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
>

6. ## Re: 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1