Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort currency (excel2000)

    I have 2 columns as follows (text format) imported into excel
    col -1in $ (finally) ____ col2-in

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort currency (excel2000)

    Web pages are "coded" in HTML. HTML ignores multiple spaces. You can use the <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags around text that should preserve the spacing as typed by you. See <!help=19>Help 19<!/help> for information about inserting tags into your posts.

    It is also possible to create a table in a post, see <!post=Making Tables in the Lounge,162644>Making Tables in the Lounge<!/post> and there is a macro available to copy an Excel table into a post, see <post#=422659>post 422659</post#> for details.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort currency (excel2000)

    I am not sure what you "have" and what you "want".

    In addition to Hans' suggestions for better formatting the data for us to see what you are after, you might create a sample file (void of any proprietary info) and show on one page what you have and explain (or show on another page) what you want. You should also explain if you want this "new output" in addition to the old or turn the old output into the new output.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    First of all -my apologies.
    My small attachment should make things clearer.
    Column "A" should contain only dollars and column "B" should contain equivalent pounds.
    I would like to turn the old output into the new output formatted as currency.
    "US $" can just be "$" in output.
    Thanx
    Smbs

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    Sorry forgot attachment

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort currency (excel2000)

    At the end of the "conversion" what should the data look like?

    I see both dollars and pounds in both columns and some blank items. SHould the blanks be calc'd, should the items be moved?

    If this is a one-time conversion, I would tend to use formulas (easier to check and modify) then to try to write and debug a macro
    You can convert the $ in col A and the

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    Steve -thanks for bearing with me!!!
    Original input could be $'s in either column if $'s are in column "b" then they should first be transferred to column "a" and then equivalent sterling value should be placed into column "b". The same for sterling however if originally found in column "a" they should first be transferred to column "b".
    The end result should be a table with no blanks -dollars in column "a" and the equivalent sterling value in column "b".
    This is going to be done routinely therefore a macro seems to be required.
    Hope things are clearer
    Regards
    Smbs

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort currency (excel2000)

    Does this do what you want?

    Select the range (A1:b9) in your example and run the macro. I used 1.9345 as teh conversion value, to get the numbers you got (change as desired). It calculates the dollars for the row (either by dinding dollars in A or [img]/forums/images/smilies/cool.gif[/img] or by converting a pound from C or D. After getting the dollar values the poind value is converted from the dollar amount. They are both rounded to 2 decimals at the end. and the cells formatted.

    Steve

    <pre>Option Explicit
    Sub SMBS_Convert()
    Dim rng As Range
    Dim rCell As Range
    Dim dCurrConvert As Double
    Dim sDollar As String
    Dim sDollar2 As String
    Dim sPound As String
    Dim sTrim1 As String
    Dim sTrim2 As String
    Dim iLenD As Integer
    Dim iLenP As Integer
    Dim AWF As WorksheetFunction

    dCurrConvert = 1.9345
    sDollar = "US $"
    sPound = "

  9. #9
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    Tried it but get runtime error "13"
    Type mismatch
    at code position --- sTrim1 = UCase(Trim(rCell))
    I am trying to see why
    Smbs

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort currency (excel2000)

    sounds like at that time in code rcell does not have a string value in it.

    When you get the error and you are in debug mode, go to the immediate window and enter:
    ?rcell.address
    to determine what cell is causing the problem and also
    ?rcell.value

    to determine its value. Numbers (in xl97 at least) get converted alright so they don't have to be just text (though all your examples were). I could only get this error if the cell had an error value in it.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    I'm in debug mode
    itermed win as follows
    ?rcell.address
    $A$1:$A$9
    ?rcell.value after pressing "enter" I again get runtime error 13
    type mismatch
    Smbs

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort currency (excel2000)

    Although it's unusual, I get the same error. It works for me if I replace the line

    For Each rCell In rng

    with

    For Each rCell In rng.Cells

  13. #13
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    Hans
    Your suggestion did the trick!! no runtime errors now
    I get the correct results but instead of column "a" showing dollar sign it shows my local currency sign which is not $--I should be able to work it out on my own from here but who knows?!
    Thanx Steve and Hans
    Smbs

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort currency (excel2000)

    Excel interprets the $ sign in the line

    rng.NumberFormat = "$#,##0.00"

    as a general indicator for currency, to be replaced with whatever the user's system settings specify. To force Excel to use the US dollar, change it to

    rng.NumberFormat = "[$$-409]#,##0.00"

    (409 is the code Windows uses for the USA)

  15. #15
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort currency (excel2000)

    Thanx HansV
    I was getting there slowly by trying to understand line below "[$

Page 1 of 2 12 LastLast

Posting Permissions

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