Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Can someone help me with my VBA funtion

Posted on 2006-04-11
Medium Priority
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 86

Accepted Solution

Mike Tomlinson earned 1000 total points
ID: 16431831
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 15

Author Comment

by:Juan Ocasio
ID: 16432104
That'll do it!



Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Make the most of your online learning experience.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

577 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