finding formulas in a workbook
Posted by Marcus on December 08, 2000 1:05 AM
I need some help with a macro which should help me find a column on every worksheet in a workbook which contains formulas and convert them to values.
Problem is that this column can appear anywhere in the sheets.
When it finds the column it should convert it to values, this I think I can work out rather easily with paste special values. What is hard for me is getting the "find" formula right. Is it possible to look just for values in a sheet?
Thanks!
Posted by Celia on December 08, 2000 1:32 AM
Marcus
If you mean that you need to find the column number of the column in question, provided you can establish criteria that is/are specific to the column, with VBA code it should always be possible to locate the column.
Post again with some specifics if you need some code.
Celia
Posted by Marcus on December 08, 2000 2:18 AM
Yes, say I have a column with the heading "Price of apples" somwhere in more than one sheet in the workbook. I want to find this column wherever it appears and replace the formulas it contains with values. It might look like this:
Price of apples
=vlookup(blah,blah,blah)
=vlookup(blah,blah,blah)
=vlookup(blah,blah,blah)
etc.
And if it would be possible to use the same code if the heading were changed to "Prices of pears" globally througout the workbook it would be even better. I guess one could look for a string which has the word "Prices" in it.
Possibly it could be done with range.find in one way or the other. But I haven't succeded so far.
Cheers/Marcus
Posted by Ivan Moala on December 08, 2000 2:54 AM
Marcus
Is it just this column with the formulas you want replaced
OR replace ALL formulas in ALL sheets ??
Ivan
Posted by Marcus on December 08, 2000 2:59 AM
Posted by Marcus on December 08, 2000 3:01 AM
Well, basically I could replace all formulas since there are no others in there (at the moment). But it would be nice to know how to replace just this one column.
/Marcus
Posted by Celia on December 08, 2000 3:12 AM
Marcus
Check whether this works :-
Sub Fix_Values()
Dim ws As Worksheet, cell As Range
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Activate
For Each cell In rangeToCheck
If cell.Value = "Price of Apples" Or cell.Value = "Price of Pears" Then
cell.EntireColumn.Copy
cell.EntireColumn.PasteSpecial Paste:=xlValues
End If
Next cell
Next ws
Application.CutCopyMode = False
End Sub
"rangeToCheck" would be the range of cells conyaining the headings.
Celia
Posted by Ivan Moala on December 08, 2000 3:18 AM
To clear all formulas in awokbook then try this;
Sub ClearFormulas()
Dim Sh
Dim FormulaCells As Range, Cell As Range
On Error Resume Next
For Each Sh In ActiveWorkbook.Sheets
Set FormulaCells = Sh.Range("A1").SpecialCells(xlFormulas, 23)
If FormulaCells Is Nothing Then GoTo skip
For Each Cell In FormulaCells
Cell.Formula = Cell.Value
Next Cell
Set FormulaCells = Nothing
skip:
Next Sh
End Sub
To clear all formulas in a given area then you
would have to look @ searching for the column
header and using the application.intersect command
to get you info cleared.
Ivan
Posted by Marcus on December 08, 2000 3:24 AM
Thankyou Celia and Ivan!
Much appreciated!
