Selecting cells with certain formula?

  • Thread starter Thread starter Alex Li
  • Start date Start date
A

Alex Li

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.
 
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.)
 
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
 
One more method which doesn't involve formulas.

Select ayour range and Edit>Find

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
 
Back
Top