By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

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

P: 57
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 "specialcells" to find formula.

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


prakash
Apr 16 '08 #1
Share this Question
Share on Google+
3 Replies


kadghar
Expert 100+
P: 1,295
...

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.

Expand|Select|Wrap|Line Numbers
  1. Dim a
  2. For Each a In Cells
  3.     If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3
  4. Next
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.

Expand|Select|Wrap|Line Numbers
  1. Dim a
  2. For Each a In Range("A1:K100")
  3.     If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3
  4. Next
well, HTH
Apr 16 '08 #2

P: 57
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.

Expand|Select|Wrap|Line Numbers
  1. Dim a
  2. For Each a In Cells
  3.     If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3
  4. Next
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.

Expand|Select|Wrap|Line Numbers
  1. Dim a
  2. For Each a In Range("A1:K100")
  3.     If Mid$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3
  4. Next
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.
Apr 17 '08 #3

kadghar
Expert 100+
P: 1,295
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
Expand|Select|Wrap|Line Numbers
  1. cells(1,1).value = "'" & cells(1,1).formula
  2. 'or
  3. range("A1").value = "'" & range("A1").formula
well, hth
Apr 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.