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