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 + 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
3 Replies

 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 Dim a For Each a In Cells     If Mid\$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3 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 Dim a For Each a In Range("A1:K100")     If Mid\$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3 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 Dim a For Each a In Cells     If Mid\$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3 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 Dim a For Each a In Range("A1:K100")     If Mid\$(a.Formula, 1, 1) = "=" Then a.Font.ColorIndex = 3 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

 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 cells(1,1).value = "'" & cells(1,1).formula 'or 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. 