Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changing between nearest $ & nearest Thousand (Excel 97)

    Can someone teach me in the shortest way on how to write a program (using VBA in Excel) to toggle the numbers to the nearest $ & thousand with the press of a button within the same worksheet.

    for example in the worksheet that I've attached, the numbers are to 2 decimal place. If I want to show the numbers to the nearest thousand, what is the fastest way to do it without using the custom setting under Format cells.

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

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    Umm, your attachment didn't make it to the Lounge. Can you try again? The file should be below 100 K, and please be aware that previewing your post will clear the attachment box.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    Your attachment didn't get attached.

    You also need to be a little more specific about what you want:

    1- When you "toggle the numbers to the nearest $ and thousand," do you mean that you want to switch between exact dollars and thousands of dollars so that each time you press the button you flip to the other display?

    2- If a cell contains $123,456 and you flip to thousands of dollars, do you want to display $123 or $123,000?
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    I guess I'd argue that using Custom Formatting -IS- the best way to this. Here's a macro to do it, edit to your preferred formats:

    Sub ToggleThouCurrFormat()
    Dim rngCell As Range
    Dim strNF As String
    Const strNFC2D As String = "$#,000.00_);<!t>[red]($#,000.00)" ' 2 dec place US currency style
    Const strNFThou As String = "$#0,_);<!t>[red]($#0,)" 'thousands US currency style
    For Each rngCell In Selection
    strNF = rngCell.NumberFormat
    If strNF = strNFC2D Or strNF = strNFThou Then
    Select Case CBool(InStr(strNF, "."))
    Case True
    rngCell.NumberFormat = strNFThou
    Case False
    rngCell.NumberFormat = strNFC2D
    End Select
    End If
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    May 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    To be more specific, what I mean is when the number in the cell is $123,456.98, I want to switch it to $123 or $123,456.98 easily with the press of a button.
    Where should I type the custom formatting that you've shown me in the excel file (which I''ve attached)? Do I do it under record macro or under visual basic.
    Can you show me all the steps. It'd be of a great help if I can master the basic of this function so that I can apply it the all my other files.

    thank you <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  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: changing between nearest $ & nearest Thousand (Excel 97)

    I would put it into your persnol.xls file so it will be available with any file you open.

    Look at the tutorial <post#=118382>post 118382</post#>

    Then copy the macro already provided by John <post#=249672>post 249672</post#> (I think this does want you want) to the personal.xls file.

    Add it to a toolbar button and it will always be on a toolbar ready to use.

    Steve

  7. #7
    New Lounger
    Join Date
    May 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    thanks steve

    I'll try this out.
    Will ask for more help if I'm still lost.

    Minyi

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    By all means post back if you have problems.

    If the formats I used are not what you want, use the macro recorder to record the formats you want. Turn on the recorder and change the formats back and forth how you want them on a sample cell, turn the recorder off and find the recorded code. Then copy the formats from your recorded macro over the formats I used above on the two "Const" Lines. If the macro I provided doesn't find an exact match to the formats, it won't do anything.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    Hi,

    A different but possibly simple approach might be to just have separate sheets - one for Dollars and one for Thousands, with the second sheet just set up to copy all the data from the first, but with the appropriate formatting adjustments.

    Alternatively the data could be entered into them both together by having both sheets active when the data is entered.

    For a relatively inexperienced user the KIS principle (Keep It Simple) is often a very good way to go.

    Good Luck!

    Peter Moran
    Two heads are always better than one!

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: changing between nearest $ & nearest Thousand (Excel 97)

    Hi minyi_chua,

    If a possible loss of precision isn't important, you could wrap the relevant calculations & values with a ROUND formula such as:
    =ROUND(CalculationOrValue,A1)
    where you put the required required level of precision in A1 (eg 2 for 2 decimal places, -3 for thousands).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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