Paste Valuing Add-In Formulas

danpassfield

New Member
Joined
Mar 26, 2004
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Mar 26, 2004
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
That's good - it makes things easy:

Code:
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

New Member
Joined
Mar 26, 2004
Messages
5
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

New Member
Joined
Mar 26, 2004
Messages
5
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Dan

Give this a try:

Code:
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Actually Dan I wasn't very careful when I came to posting that update - it should have looked like this:

Code:
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!
 

Forum statistics

Threads
1,082,176
Messages
5,363,577
Members
400,751
Latest member
wheddingsjr

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top