How to delete all formula in the excel 2000....

[prakashsakthivel]

Hi Members,

What I want to know is, I want to detect formula which cells has formula and converted as text.

let us example,
One excel workbook have 4 sheet. all cells of each sheet contains formula
like internal link with other worksheet and other workbook.
I have used "specialcel ls" to find formula.

I need that is any way to detect all formula at one time.


prakash

[kadghar]

...

I need that is any way to detect all formula at one time.
prakash

I think an easy way will be checking if each cell's formula starts with "=", then you'll know it's a formula, e.g.

[CODE=vb]Dim a
For Each a In Cells
If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorInd ex = 3
Next[/CODE]

This example checks every cell, and turns RED all the cells with a formula. But since it'll check all the cells of the worksheet, i may take a while, so you can define a range instead, to make it faster, e.g.

[CODE=vb]Dim a
For Each a In Range("A1:K100" )
If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorInd ex = 3
Next[/CODE]

well, HTH

[prakashsakthivel]

I think an easy way will be checking if each cell's formula starts with "=", then you'll know it's a formula, e.g.

[CODE=vb]Dim a
For Each a In Cells
If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorInd ex = 3
Next[/CODE]

This example checks every cell, and turns RED all the cells with a formula. But since it'll check all the cells of the worksheet, i may take a while, so you can define a range instead, to make it faster, e.g.

[CODE=vb]Dim a
For Each a In Range("A1:K100" )
If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorInd ex = 3
Next[/CODE]

well, HTH

Thanks for reply,

It is working nice.

Actually I want to know is that I have to transfer all cells (formula) to other workbook, but this workbook contains formula, constants ,,, then whenever required, those formula are transferred to old workbook.

What I have done that when transfering data to other workbook I created comments to each cell for avoidig path of old file that means formula to text.

specialcells is used to identify formula.

then whenever needed (formula) those text(formula) are transferred to formula.
with help of comments formula cells has comments for speed.

It is very slow while processing that's why I want to know that
is it possible to identify or change all formula at one time.


Thanks
Prakash.

[kadghar]

Thanks for reply,

It is working nice.

Actually I want to know is that I have to transfer all cells (formula) to other workbook, but this workbook contains formula, constants ,,, then whenever required, those formula are transferred to old workbook.

What I have done that when transfering data to other workbook I created comments to each cell for avoidig path of old file that means formula to text.

specialcells is used to identify formula.

then whenever needed (formula) those text(formula) are transferred to formula.
with help of comments formula cells has comments for speed.

It is very slow while processing that's why I want to know that
is it possible to identify or change all formula at one time.


Thanks
Prakash.

actually, the excel's VBA is very slow while working cell by cell with excel. What i recomend you to do is this:

i dont understand what you mean with comments for speed. But an easy way to
transform a formula into text, inside excel, is adding a semicolon an the begining, i.e.
if you have =sum(A1:A10)
just write '=sum(A1:A10)
and it wont show the result, but the formula itself

to achieve this, just do something like
[CODE=vb]
cells(1,1).valu e = "'" & cells(1,1).form ula
'or
range("A1").val ue = "'" & range("A1").for mula[/CODE]

well, hth