Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro help Excel 2000 (Excel 2000)

    Hi,

    There is an existing formula on a sheet =(B4*0.25) that needs to change to =IF(B4*0.25="","",B4*0.25) . There are several rows that need this =if formula however the % changes in each row. I created a macro, selecting the original formula before recording in order to paste it in =if formula.

    ActiveCell.FormulaR1C1 = "=IF(RC[-3]*0.25=0,"""",RC[-3]*0.25)"
    Range("E5").Select

    The macro works when I run it. Is there something other than manually fixing the %'s to have them change?

    I hope this makes sense?!?!?!?!?!?

    Thanks for your help!!!!!

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

    Re: Macro help Excel 2000 (Excel 2000)

    Do you mean you lose the cell formatting?

    ActiveCell.NumberFormat = "0%"

    or

    Selection.NumberFormat = "0%"

    should fix this.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Oct 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help Excel 2000 (Excel 2000)

    Hi John,

    Thanks for your post. I am not losing the formatting when the macro runs. Below is a crude sample of what is happening:

    Value Original Formula Macro Results
    Test 1 100 =B2*0.25 =IF(B2*0.25=0,"",B2*0.25)
    Test 2 200 =B3*0.4 =IF(B3*0.25=0,"",B3*0.25)
    Test 3 300 =B4*0.65 =IF(B4*0.25=0,"",B4*0.25)

    I am interested in knowing if there is something that can be created in a macro that can not only improve the original formula but somehow change the %'s in the formula for the appropriate row it is in. The answer may be having manually change the %'s and a macro is not the solution?!?!?!?

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

    Re: Macro help Excel 2000 (Excel 2000)

    <P ID="edit" class=small>(Edited by macropod on 16-Sep-03 17:11. Alternate formatting solution offered.)</P>Hi Kathy,

    Try something like:

    Sub ChangeFormula()
    Dim TestCell As Object
    Dim CellFormula As String
    For Each TestCell In Selection
    CellFormula = TestCell.Formula
    If Left(CellFormula, 1) = "=" Then
    CellFormula = Right(CellFormula, Len(CellFormula) - 1)
    CellFormula = "=if(" & CellFormula & "=0," & Chr(34) & Chr(34) & "," & CellFormula & ")"
    TestCell.Formula = CellFormula
    End If
    Next TestCell
    End Sub

    This works by preserving the contents of the original formula in each cell and incorporating the old formula into the new one. That way, there's no need to worry about updating row references.

    Alternatively, if all you're trying to achieve is hiding zero results, you could use a custom number format (eg #.00%;-#.00%; Note the final semi-colon). The advantage of using formatting to achieve thie result is that your spreadsheet will perform faster and the hidden zeros are still stored as numbers (whereas nuls (ie "") are not). You can do the custom formatting manually (via Format|Cells|Number|Custom or programatically as per the following macro:

    Sub ChangeFormat()
    Dim TestCell As Object
    Dim CellFormula As String
    For Each TestCell In Selection
    CellFormula = TestCell.Formula
    If Left(CellFormula, 1) = "=" Then TestCell.NumberFormat = "#.00%;-#.00%;"
    Next TestCell
    End Sub

    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
  •