0

I have a macro I'm writing to format a daily report; part of which compares a week-over-week percent change. I prefer to use a "+0%;-0%" format as opposed to the standard "0%;-0%", but when I apply filters, I am losing the number context, and associated number filters options (ex. "above average").

Any ideas?

I tried copy/pasting as values, instead of leaving the formula and while that seemed to fix the first % Col range, it did not fix the others (although they are correctly pasted as values)

''''''''                    
 ''Call my FindAll function to find each column with the header "% chg"
 ''''''''

    Dim matches As Collection, m
    Dim mycell As Range, c As Range

    Dim strFindMe As String
    Dim myCell As Range


            strFindMe = "% Chg"


            Set matches = FindAll(rgHeader, strFindMe)
            For Each m In matches
                Dim ColPct As Range
                    Set c = m.Offset(1, 0)
                    Set ColPct = Range(c.Address, Cells(RgSummary.Row, _
                        Range(c.Address).Column))

 ''''''''                    
 ''Calculate the wk-wk % chg for each column with header "% Chg"        
 ''''''''   

                        For Each myCell In ColPct
                            ''calculate Prct Chg
                                myCell.FormulaR1C1 = "=iferror(IF(AND(rc[-6]<>"""",rc[-3]<>""""),(rc[-3]/rc[-6]-1),""""),""N/A"")"
    '                            myCell.NumberFormat = "+0%;-0%"
                        Next

                        With ColPct
                            .Copy
                            .PasteSpecial xlValues
                            .NumberFormat = "+0%;-0%"
                        End With
              next m

Goal: percent change has format "+0%;-0%" and is read by excel filters as a number, not a string

LE FE
  • 45
  • 7
  • How about you use the standard one `"0%;-0%"` and set up conditional formatting to easily differentiate between negatives and positive numbers. Just a thought. – Ricardo A Mar 27 '19 at 16:34
  • thanks, there actually is conditional formatting as well (red v blue) but I prefer to have the "+" symbol there for added emphasis, and to set it apart from other "percent of total" columns – LE FE Mar 27 '19 at 18:30

1 Answers1

0

turns out it wasn't the number formatting, it was the piece of the formula that said if then "" that was leaving invisible text strings in my columns

fixed it with this code I found via ElderDelp here, so thanks, Man!

Return empty cell from formula in Excel

For Each myCell In ColPct
                    ''calculate Prct Chg
                        myCell.FormulaR1C1 = "=iferror(IF(OR(rc[-6]<>"""",rc[-3]<>""""),(rc[-3]/rc[-6]-1),""""),""N/A"")"
                        myCell.NumberFormat = "+0%;-0%"
                        If Len(myCell.Text) = 0 Then
                            myCell.ClearContents
                        End If
                Next
LE FE
  • 45
  • 7