Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Posts
    88
    Thanks
    3
    Thanked 1 Time in 1 Post

    Macro doesn't work

    I am using Microsoft Excel 2007, 32-bit, on a Windows 7 (64-bit) computer.

    I am having troubles running a macro that was written by somebody else as I am no expert in these things. It works on his computer but not on mine. We've tried to troubleshoot the issue but have gotten nowhere and it is taking too much time (we are on different continents!).

    In a nutshell, the macro is supposed to increase/decrease by an x % all the values in cells that are formatted as $ currency (Format Cells -> Currency -> Decimal Places: 2; Symbol: $; Negative numbers: Black font with parentheses) in each worksheet that is part of the workbook. So by inserting the % increase/decrease in cell O2 of Sheet3 and launching the macro, any values in cells formatted as $ currency would be automatically increased/decreased by the x %. (The only exceptions would be if the cell has no value in it, i.e. it is blank, or has something in it that is not considered a currency amount.)

    The macro works fine on the author's computer. On mine nothing happens, i.e. the $ amounts are not changed by the % increase/decrease.

    Here is the code:
    Code:
    Sub Test()
    Dim i As Long
    Dim rngC As Range
    
    Application.ScreenUpdating = False
    
    For i = 1 To Sheets.Count
       For Each rngC In Sheets(i).UsedRange
          If Len(rngC) > 0 And IsNumeric(rngC) And rngC.NumberFormat = _
             """$""#,##0.00_);(""$""#,##0.00)" Then
             rngC = Round(rngC * (1 + Sheets("Sheet3").Range("O2")), 2)
          End If
       Next
    Next
    
    Application.ScreenUpdating = True
    End Sub
    Could somebody please run it and tell me what happens? You can use a blank workbook, format a few cells in several worksheets as described above (Format Cells -> Currency, etc. etc.), insert some $ values, insert a % increase/decrease in cell O2 of Sheet3, launch the macro.

    I am open to suggestions on how the macro needs to be written/changed in order to run on my PC.

    PS: Yes, the Macro Settings in the Trust Center of my Excel software is set to "Enable all macros"!!
    Last edited by tb75252; 2014-12-14 at 20:05.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    When I check the currency format, I get:
    "$#,##0.00_);($#,##0.00)"
    not:
    """$""#,##0.00_);(""$""#,##0.00)"
    Using the "$#,##0.00_);($#,##0.00)" version in the If test works for me.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. The Following User Says Thank You to macropod For This Useful Post:

    tb75252 (2014-12-18)

  4. #3
    Star Lounger
    Join Date
    Dec 2009
    Posts
    88
    Thanks
    3
    Thanked 1 Time in 1 Post
    Quote Originally Posted by macropod View Post
    When I check the currency format, I get:
    "$#,##0.00_);($#,##0.00)"
    not:
    """$""#,##0.00_);(""$""#,##0.00)"
    Using the "$#,##0.00_);($#,##0.00)" version in the If test works for me.
    That seems to work! Thank you very much for your help.

Posting Permissions

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