+ Reply to Thread
Results 1 to 4 of 4

simple vba formula results in concatenated numbers

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Vancouver BC
    MS-Off Ver
    Excel 2007
    Posts
    7

    simple vba formula results in concatenated numbers

    am new to VBA in Excel. need to show sum and not concatenated values
    have a userform w 60 fields for numbers as follows:
    a) UserForm1.TextBox34 to UserForm1.TextBox41: 8 fields-CAD currency
    b) UserForm1.TextBox42 to UserForm1.TextBox49: 8 fields-USD currency
    c) UserForm1.TextBox50 to UserForm1.TextBox57: 8 fields-exchange rate
    d) UserForm1.TextBox58 to UserForm1.TextBox65: 8 fields-Total CAD currency => the formula here works across the board
    UserForm1.TextBox58.value = Format(UserForm1.TextBox34.value+UserForm1.TextBox42.value*UserForm1.TextBox50.value, "###,##0.00")
    e) UserForm1.TextBox66 to UserForm1.TextBox73: 8 fields-local taxes in CAD currency
    f) UserForm1.TextBox74 to UserForm1.TextBox81: 8 fields-federal taxes in CAD currency
    g) UserForm1.TextBox82 to UserForm1.TextBox89: 8 fields-total for first column in CAD => the formulas here works as well across the board
    UserForm1.TextBox82.value = Format(UserForm1.TextBox34.value + UserForm1.TextBox42.value * UserForm1.TextBox50.value + UserForm1.TextBox66.value + UserForm1.TextBox74.value, "###,##0.00")

    have a grand total formula pulling in all values, and this one works as well; so i have 4 simple addition formulas, and the local & fed tax formulas do not.

    however, the formulas for the sub-total for the local taxes and the federal taxes do not work; the results show a concatenation of the 8 values rather thn the sum of the 8 values; the formula for the local tax is as follows:
    UserForm1.TextBox110.Value = Format(UserForm1.TextBox66.Value + UserForm1.TextBox67.Value + UserForm1.TextBox68.Value + UserForm1.TextBox69.Value + UserForm1.TextBox70.Value + UserForm1.TextBox71.Value + UserForm1.TextBox72.Value + UserForm1.TextBox73.Value, "0.00")

    have rewritten the formulas and still these two tax formulas result in concatenated numbers rather than the sum.

    appreciate any assistance on the above.

    would have u/l worksheet but it's roughly 4MB now.

    thank you..

    rmon
    Attached Files Attached Files
    Last edited by RJAM99; 04-24-2014 at 06:48 PM. Reason: uploaded pic of userform

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: simple vba formula results in concatenated numbers

    Hi RJAM99

    Welcome to the Forum!!

    Without seeing your actual File I can't test this but it'll be something like this...

    In the UserForm Module
    Please Login or Register  to view this content.
    In a General Module
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    03-26-2014
    Location
    Vancouver BC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: simple vba formula results in concatenated numbers

    Hi jaslake,

    thanks for the input. tried adding your code but kept getting a bug, pointing to the line "sVal(i) = Val(.Controls("TextBox" & i).Value)".

    anyway, got it solved by forcing the VBA to recognize the textboxes as numerical values and not as text. Added a multiplier (*1) to one of the values.

    I get the desired results now.

    Thanky ou for your input.

    regards.

    rjam99
    Last edited by RJAM99; 05-05-2014 at 05:24 PM.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: simple vba formula results in concatenated numbers

    Hi rmon

    Glad you got it resolved.

    If this resolves your issue please mark your Thread as SOLVED.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click SaveYou're welcome...glad I could help.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replacing Filtered Data with Concatenated Results
    By easbrrx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-18-2013, 04:20 AM
  2. [SOLVED] Can we use a simple Excel formula to get the same results as the VBA macro?
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 07:56 PM
  3. Formatting Concatenated results in 1 cell
    By David_S_Walker in forum Excel General
    Replies: 6
    Last Post: 07-18-2011, 08:59 AM
  4. date field not updating concatenated results
    By Scooby-Ray in forum Excel General
    Replies: 2
    Last Post: 07-28-2008, 05:47 AM
  5. Replies: 2
    Last Post: 07-25-2006, 01:50 PM

Tags for this Thread

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