Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Bryant Smith   on Sep 20 In MS Office Category.

  
Question Answered By: Edwin Chavez   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
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.

Share: 

 

This Question has 4 more answer(s). View Complete Question Thread

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


Tagged: