CODEWITHSUNDEEP
vbaexcel
www3
www3

Reputation: 279

An if statement to determine if a cell contains a specific formula

I am trying to write a program that will copy my entire workbook and paste it into a new workbook.

However I for each cell that it copies, if the cell is a =CIQ() function (there are parameters inside (between 2 and perhaps more than 7))

I would like it to paste the value of the cell (and formatting but that matters less and I'm sure I can figure that part out), but if the cell doesn't contain a =CIQ() function I would like it just do a normal copy and paste.

I'm really new to VBA, so I am wondering what the syntax is for an if statement that will determine whether the cell is a CIQ() function or not.

Thanks

Upvotes: 0

Views: 1450

Answers (3)

brettdj
brettdj

Reputation: 55672

Code like below will replace any formulae beginning with =CIQ with the value of the formula.

Sub RemoveCIQ()
Dim rng1 As Range
Set rng1 = Cells.Find("=CIQ", , xlFormulas, xlPart)
Do While Not rng1 Is Nothing
rng1.Value = rng1.Value
Set rng1 = Cells.Find("=CIQ", , xlFormulas, xlPart)
Loop
End Sub

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6771

I would also suggest, if it'll work for your needs, rather than copy each sheet/cell into a new workbook, make a copy of the workbook, then just use a find to replace all CIQ formulas with values. This can also be done in VBA:

http://support.microsoft.com/kb/126093

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6771

As your looping through each cell, you can check if the formula starts out "=CIQ"

If Left(ActiveSheet.Cells(i, j).Formula, 4) = "=CIQ" Then
    'Copy value and formatting
End If

Upvotes: 2

Related Questions