Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I wonder if there is any way of searching within formulas in a sheet

or coping all formulas to a text file (to carry on search e.g. in notepad)?

I have to update a few formulas relating to the other sheet.

The sheet contains hundreds of formulas.

And I am sick when I think of doing it manually - one by one.

I dug this up from a loong time ago so please excuse the coding but it

does work and probably doees what you want. As you can

see I type the range into the code.

The two important bits are locF = cel.Formula and cel.Formula = locF.

You could also do a Ctrl H and just search & replace if it's a simple.

Sub subChangeCells()

' Massage formulas in a hard coded range.

Dim locF As String

Dim locFStart As String

Dim locFEnd As String

Dim cel As Range

Dim locN As Integer

Dim locN1 As Integer

Dim locChr As Integer

Dim locFRange As String

Dim locChr2 As Integer

Dim locLen As Integer

locN = 1

locChr = 1

locFEnd = ","""")"

For Each cel In Range("a8:an8")

locF = cel.Formula

locChr = InStr(locF, "!")

locF = Mid$(locF, 1, locChr) & "$" & Mid$(locF, locChr + 1, 1) &

"$" & Mid$(locF, locChr + 2)

cel.Formula = locF

Next cel

MsgBox "Change cells Done"

'

I see your problem, if you use the find facility under Edit|Find or

Ctrl+F, even if you ask it to look in formulae it will also find

cells where the search string is not in a formula.

However, the above search functions will only search within selected

cells if more than one cell is selected. So if you were only to

select cells which had formulae in _before_ doing a Ctrl+F (Find) or

Ctrl+H (Find and replace), it should go a long way to helping you.

Below is a macro that will select only those cells on the active

sheet whose contents begin with the equals sign ("=").

Run it once, then do your Ctrl+F or Ctrl+H.

Here's the macro:

Sub SelectFormulaCellsOnly()

For Each cll In ActiveSheet.UsedRange.Cells

If Left(cll.Formula, 1) = "=" Then

Set FirstCell = Range(cll.Address)

Exit For

End If

Next cll

Dim myrange As Range

Set myrange = FirstCell

For Each cll In ActiveSheet.UsedRange.Cells

If Left(cll.Formula, 1) = "=" Then

Set myrange = Union(myrange, Range(cll.Address))

End If

Next cll

myrange.Select

End Sub

Tell me how you get on. It isn't perfect as cells formatted as text

containing an equals sign ("=") are still selected, as are cells

beginning thus:

'=

If this is of no use I will try to write a macro to copy all formulae

to a text file.

Sometimes if the sheet automatically update, some links don't work.

Then another pop out window will ask: continue or edit links?

In VBA, how to get around that? For instance, always continue.

You could try Tools|Options|Calculation Tab, and clear the 'Update

remote references' chackbox.

Beyond that I don't know, unless you're using xl 2003 when you should

visit:

support.microsoft.com/default.aspx

A bit of further research yields a much better answer. All of that

macro below can be replaced with just one line:

cells.SpecialCells(xlFormulas).Select

The equivalent manually is:

Make sure no multicell range is selected (that would restrict the

resultant selected cells to within that multicell selection)

Edit dropdown menu|Go to...

Click the Special button

Select Formulas, (leave all checks in place)

Click OK

THEN do your find/replace.

Didn't find what you were looking for?
Find more on searching within formulas
Or get search suggestion and latest updates.

Related Topics:

- Search within closed workbooks
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- VBA or array formula?
- VBA code to add values in two cells which have formulas
- combining formula
- How to add several conditions for formula "SUMIF"
- formula help but not vba please
- Formula using range name in VBA code
- Three Leveled Formula
- R1C1 Formula
- Paste Formula help
- Loop in formula
- Copy down formula with vba
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Stuck on copying formula from 1 sheet to another
- Named formula/Windows dialog boxes
- cells containing formula that refer to user-defined VBA function
- sum formula
- Manually creating a row outline with no formula anywhere
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Populate Formula(Function) in rows
- Blank Formula bar
- Disabling Formulas, Not calculation