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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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

ADVERTISEMENT

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

ADVERTISEMENT

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top