MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

searching within formulas

  Asked By: Bryant    Date: Sep 20    Category: MS Office    Views: 1874

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.



5 Answers Found

Answer #1    Answered By: Leroy Schmidt     Answered On: Sep 20

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"

Answer #2    Answered By: Edwin Chavez     Answered On: Sep 20

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
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.

Answer #3    Answered By: Burk Martin     Answered On: Sep 20

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.

Answer #4    Answered By: Hubert Taylor     Answered On: Sep 20

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

Answer #5    Answered By: Lurlene Fischer     Answered On: Sep 20

A bit of further research yields a much better answer. All of that
macro below can be replaced with just one line:


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.