Paste Valuing Add-In Formulas

[danpassfield]

Hi Guys,

We use Cognos Controller as a reporting tool, and retrieve into Excel for analysis work and Management Reporting. The Cognos Controller is an "Add In" within excel, and all formulas start with =cc.f.

I am trying to create a macro, which enables us to paste value the Cognos Controller formulas, but to leave the normal excel formulas (adding rows/columns etc) and was wondering if anyone had come across this and what the best way is achieve my goal at all?

Any help woudl be great.

Kind Rgds
Dan

 

[Richard Schollar]

Hello Dan

You could check all formulas to see if they contain the text string "cc.f." and if so, convert these formulas to values. The problem with this approach is if you have a formula like:

=SUM(cc.f.SomeFunction(A1,B1,C1),cc.f.SomeOtherFunction(A2,B2,C2))

and wanted to retain the outer SUM() as it would just get converted to a value like all the other cc.f. containing functions.

 

[danpassfield]

Hi Richard,

Thank you for your reply. But the (cc.f) formulas are not part of a normal formula, they are seperate. So the normal excel funcitonalities i am talking about (=sum etc) do not contain the retrieve formulas.

To summarise imagine cells A1:a10 containing the cc.f retrieve formulas, and cell a11 containing =sum(a1:a10), and what i would like is to be able to paste special value cells A1:A10, but leave the formula that is in cell a11 as a formula.

Am sorry if i wasnt that clear in my original post.

Kind Rgds
Dan

 

[Richard Schollar]

That's good - it makes things easy:

Sub test()
Dim rng As Range, cell As Range
Dim xlCalc As Long
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
With Application
    xlCalc = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each cell In rng
    If InStr(1, cell.Formula, "cc.f.", vbBinaryCompare) > 0 Then cell.Value = cell.Value
Next cell
Application.Calculation = xlCalc
End Sub

Plonk the above in a standard module and with the sheet with the formulas visible on the screen run it.

 

[danpassfield]

Richard,

Thanks for that, works like a treat. Sorry for being a pain, but one more thing, could you amend the string for me to get it to work on all sheets (including hidden ones) instead of just the selected sheet?

Yours Hopingly
Dan

 

[danpassfield]

Or would anybody else be able to help me with this string? The string that Richard kinldy included in this message worked well. I would like to do this for all sheets (including hidden ones) within a file, so could anybody edit the string from Richard to allow this to happen at all?

Many Thx
Dan

 

[Richard Schollar]

Hi Dan

Give this a try:

Sub test()
Dim rng As Range, cell As Range
Dim xlCalc As Long
Dim ws As Worksheet
For Each ws In Worksheets
  On Error Resume Next
  Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
  On Error Goto 0
  If Not rng Is Nothing Then
    With Application
      xlCalc = .Calculation
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
    End With
    For Each cell In rng
      If InStr(1, cell.Formula, "cc.f.", vbBinaryCompare) > 0 Then cell.Value   = cell.Value
    Next cell
  End If
  Set rng = Nothing
Next ws
Application.Calculation = xlCalc
End Sub

 

[danpassfield]

Thanks Richard, that works perfectly ;o) greatly appreciated!

Kind Rgds
Dan

 

[Richard Schollar]

Actually Dan I wasn't very careful when I came to posting that update - it should have looked like this:

Sub test()
Dim rng As Range, cell As Range
Dim xlCalc As Long
Dim ws As Worksheet    
With Application
      xlCalc = .Calculation
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
End With
For Each ws In Worksheets
  On Error Resume Next
  Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
  On Error Goto 0
  If Not rng Is Nothing Then

    For Each cell In rng
      If InStr(1, cell.Formula, "cc.f.", vbBinaryCompare) > 0 Then cell.Value   = cell.Value
    Next cell
  End If
  Set rng = Nothing
Next ws
Application.Calculation = xlCalc
End Sub

No particular impact on performance, just it's much neater!