Can someone help me with my VBA funtion

Posted on 2006-04-11
Last Modified: 2010-04-17
OK Here it goes:

I need to create a function that will pick up the last dollar amount in a string.  So for example, if I have the fiollowing string:

AJHY65 AAS JOHNSON AVE SLWX 3/13/06 259.22 59.90 3/08/05 6 YES

I need to return 59.90

Also each line is different with different values, the only real consistant thing is there will be two dollar amounts, which may or may not be the same, and  two dates

Can someone help me.  The more concise the better


Question by:Juan Ocasio
    LVL 85

    Accepted Solution

    If you're dollar amount is guaranteed to be the only thing with a period "." in it then:

    Option Explicit

    Private Sub Command1_Click()
        Dim data As String
        data = "AJHY65 AAS JOHNSON AVE SLWX 3/13/06 259.22 59.90 3/08/05 6 YES"
        Dim amount As String
        amount = getAmount(data)
        MsgBox amount
    End Sub

    Private Function getAmount(ByVal str As String) As String
        Dim vals As Variant
        vals = Split(str, " ")
        Dim i As Integer
        For i = UBound(vals) To LBound(vals) Step -1
            If InStr(vals(i), ".") > 0 Then
                getAmount = vals(i)
                Exit Function
            End If
        Next i
    End Function
    LVL 14

    Author Comment

    by:Juan Ocasio
    That'll do it!



    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now