Range value cells with Sumif
 

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?

Range value cells with Sumif
 

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?

Range value cells with Sumif
 

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?

Range value cells with Sumif
 

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

Range value cells with Sumif
 

whoops, forgot to paste the code in:
try this:


Code:
--------------------
Sub FindSumIf()
Cells.Find(What:="sumif", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
MsgBox ActiveCell.Value
End Sub
--------------------


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381401

Range value cells with Sumif
 

Sorry everyone - reading my original post I realize I was quite vague! I
would like to find all cells that have a sumif in them, and then range-value
the cells. So essentially, remove the formula and hardcode the result in
the cell. As if I were to select a cell, copy and paste special values.
Thanks!!


"bhofsetz" wrote in
message ...

whoops, forgot to paste the code in:
try this:


Code:
--------------------
Sub FindSumIf()
Cells.Find(What:="sumif", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
MsgBox ActiveCell.Value
End Sub
--------------------


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381401

Range value cells with Sumif
 

Simply replace the

MsgBox rngCurrent.Value

from the code that Jim Thomlinson supplied with

rngCurrent.Copy
rngCurrent.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

and you've got i

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