How to write a macro in Excel that will hardcode all the cells in a workbook that contain formulas with the letter "CIQ"?

Question

I am working on a spreadsheet that is pulling a lot of data from Capital IQ (CIQ). I would like to hardcode all these formulas. If somebody that doesn't have the CIQ plugin tries to look at the spreadsheet, all they see is #name and #value. I don't want to hardcode all the cells since some of them are useful to see how they are linked to other tabs. I am looking to hardcode only the cells that contain a CIQ formula. Thank you for your help! Miha

@MihaelaCiulianu The code of yourself - that is all are waiting from you :) — user6698332, Mar 24 '18 at 05:40

score 1 · Answer 1 · answered Mar 24 '18 at 00:02

1

Untested from mobile

Sub find()
     Dim c as range
     For each c in range(“A1:B100”).Cells
        On Error Resume Next
        If application.worksheetFunction. Search(c.value,”CIQ”) > 0 then c = c.value
     Next c
End sub

answered Mar 24 '18 at 00:02

Dude_Scott

641
5
9

Thanks, Scott. I'll check it out and let you know if it solves my problem – Mihaela Ciulianu Mar 24 '18 at 00:12
@MihaelaCiulianu make sure to change the range to what range your iterating through – Dude_Scott Mar 24 '18 at 00:23
If this solved your question, please mark as answered so other users will know it is closed – Dude_Scott Mar 25 '18 at 17:40

How to write a macro in Excel that will hardcode all the cells in a workbook that contain formulas with the letter "CIQ"?

1 Answers1