+ Reply to Thread
Results 1 to 6 of 6

cash denominations split up

  1. #1

    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


  2. #2
    Roger Govier
    Guest

    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


    <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    vezerid
    Guest

    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. #4
    Lucas Budlong
    Guest

    Re: cash denominations split up

    Kostis,

    I just tested the original and it works fine.

    Lucas


    "vezerid" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #5
    Lucas Budlong
    Guest

    Re: cash denominations split up

    The array is horizontal.


    "vezerid" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #6
    vezerid
    Guest

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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