Selecting cells with certain formula?
 

Say I have a spreadsheet with tons of formula, is there a way to
select (highlight) those that have the formula I specifiy? (say SUM,
AVERAGE, etc. I wanna do FDS actually for factset). Thanks.

Selecting cells with certain formula?
 

formatconditional formattingformula is
--
Don Guillett
SalesAid Software

"Alex Li" wrote in message
oups.com...
Say I have a spreadsheet with tons of formula, is there a way to
select (highlight) those that have the formula I specifiy? (say SUM,
AVERAGE, etc. I wanna do FDS actually for factset). Thanks.

Selecting cells with certain formula?
 

I mean "select". not formatting. say range("A1").select.

Does it matter if the parameters of the formulas are different even
though it's all the same operator? (say SUM, I am sure the xxx in
SUM(xxx) are all different), but I wanna catch all SUM cells.)

On Jun 27, 8:37 am, "Don Guillett" wrote:
formatconditional formattingformula is
--
Don Guillett
SalesAid Software
"Alex Li" wrote in message

oups.com...



Say I have a spreadsheet with tons of formula, is there a way to
select (highlight) those that have the formula I specifiy? (say SUM,
AVERAGE, etc. I wanna do FDS actually for factset). Thanks.- Hide quoted text -

- Show quoted text -

Selecting cells with certain formula?
 

Alex,

Try the macro below.

Note that asking for SUM will find SUM and SUMPRODUCT.... Ask for SUM( to prevent that....

HTH,
Bernie
MS Excel MVP


Sub FindFunctions()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFindString As String
Dim firstAddress As String

myFindString = Application.InputBox("What function to find?", Type:=2)
With Cells

Set c = .Find(myFindString, LookIn:=xlFormulas, LookAt:=xlPart)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

'Then do what you want with all the cells that have been found

' You can select them

d.Select

'or color them yellow
With d
With .Interior
.ColorIndex = 6
End With
End With

End Sub

"Alex Li" wrote in message
oups.com...
Say I have a spreadsheet with tons of formula, is there a way to
select (highlight) those that have the formula I specifiy? (say SUM,
AVERAGE, etc. I wanna do FDS actually for factset). Thanks.

Selecting cells with certain formula?
 

One more method which doesn't involve formulas.

Select ayour range and EditFind

What: SUM(

Find All.

With found cells dialog box open, hold shift key and scroll down to bottom of
list and select last in list.

Those cells will be highlighted.

Format them to a color for easy viewing.


Gord Dibben MS Excel MVP

On Wed, 27 Jun 2007 08:15:47 -0700, Alex Li wrote:

Say I have a spreadsheet with tons of formula, is there a way to
select (highlight) those that have the formula I specifiy? (say SUM,
AVERAGE, etc. I wanna do FDS actually for factset). Thanks.