Strange request - Is there a way to programmatically find any cell that has
a sumif formula in it, and value the contents of that cell?

Something like this?

Public Sub FindSumIf()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngResult As Range
Dim rngFirst As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find("Sumif", , xlFormulas)

If rngCurrent Is Nothing Then
MsgBox "Not Found"
Else
Set rngResult = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngResult = Union(rngCurrent, rngResult)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngResult.Select
End If

For Each rngCurrent In rngResult
MsgBox rngCurrent.Value
Next rngCurrent
End Sub
--
HTH...

Jim Thomlinson


"Steph" wrote:

Strange request - Is there a way to programmatically find any cell that has
a sumif formula in it, and value the contents of that cell?

Oops... The for next with the message box was out of place...

Public Sub FindSumIf()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngResult As Range
Dim rngFirst As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find("Sumif", , xlFormulas)

If rngCurrent Is Nothing Then
MsgBox "Not Found"
Else
Set rngResult = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngResult = Union(rngCurrent, rngResult)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngResult.Select
For Each rngCurrent In rngResult
MsgBox rngCurrent.Value
Next rngCurrent
End If


End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Something like this?

Public Sub FindSumIf()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngResult As Range
Dim rngFirst As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngCurrent = rngToSearch.Find("Sumif", , xlFormulas)

If rngCurrent Is Nothing Then
MsgBox "Not Found"
Else
Set rngResult = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngResult = Union(rngCurrent, rngResult)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngResult.Select
End If

For Each rngCurrent In rngResult
MsgBox rngCurrent.Value
Next rngCurrent
End Sub
--
HTH...

Jim Thomlinson


"Steph" wrote:

Strange request - Is there a way to programmatically find any cell that has
a sumif formula in it, and value the contents of that cell?

This should give you a place to start.

What do you want to do with the values of each cell that contains
=SumIf function?

HT

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=38140