By reading other posts I have been trying to learn what I am doing
wrong but so far have been unable to do so. I am sure the question
has been asked and answered but either I can't find it or don't
recognize it when I see it.

I have a worksheet with three tabs BS, IS, and TB. BS and IS are
linked to TB with SumIf functions. I want to search the worksheets
and do a paste special values for the cells containing the SumIf but
leave the other formulas alone (the ones that contain column totals).
Using Worksheet BS I have a range BS and used the following code. The
code does what I want but at the end I get the message Object Variable
or With Block Variable Not Set. I would appreciate some help on what
I am doing wrong.

Sub Macro6()
For Each Cell In range("BS")
Cells.Find(What:="=sumif", After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveSheet.Paste
Application.CutCopyMode = False
Next Cell
End Sub

Any help will be very much appreciated

Dennis

find is a range object

Find and FindNext return range objects
FindNext needs the Find range object to execute:

Try this:
Sub Macro6(
For Each cell In Range("BS"
Set x = Cells.Find(What:="=sumif", After:=ActiveCell
x.Address.Activat
Set x = Cells.FindNext(x
x.Address.Activat
Selection.Cop
Selection.PasteSpecial Paste:=xlValue
ActiveSheet.Past
Application.CutCopyMode = Fals
Next cel
End Su



----- Dennis wrote: ----

By reading other posts I have been trying to learn what I am doin
wrong but so far have been unable to do so. I am sure the questio
has been asked and answered but either I can't find it or don'
recognize it when I see it

I have a worksheet with three tabs BS, IS, and TB. BS and IS ar
linked to TB with SumIf functions. I want to search the worksheet
and do a paste special values for the cells containing the SumIf bu
leave the other formulas alone (the ones that contain column totals).
Using Worksheet BS I have a range BS and used the following code. Th
code does what I want but at the end I get the message Object Variabl
or With Block Variable Not Set. I would appreciate some help on wha
I am doing wrong

Sub Macro6(
For Each Cell In range("BS"
Cells.Find(What:="=sumif", After:=ActiveCell).Activat
Cells.FindNext(After:=ActiveCell).Activat
Selection.Cop
Selection.PasteSpecial Paste:=xlValue
ActiveSheet.Past
Application.CutCopyMode = Fals
Next Cel
End Su

Any help will be very much appreciate

Denni

lets try this again
Find and FindNext return Range objects
FindNext uses the Find Range object to execute

Sub Macro6(
For Each cell In Range("BS"
Set x = Cells.Find(What:="=sumif", After:=ActiveCell
x.Address.Activat
Set x = Cells.FindNext(x
x.Address.Activat
Selection.Cop
Selection.PasteSpecial Paste:=xlValue
ActiveSheet.Past
Application.CutCopyMode = Fals
Next cel
End Sub

or even try this

With Worksheets("BS").UsedRange
Set x = .Cells.Find(What:="=sumif", After:=ActiveCell)
Do
If Not x Is Nothing Then
x.Copy
x.PasteSpecial Paste:=xlValues
ActiveSheet.Paste
Set x = Cells.FindNext(x)
End If
Loop Until x Is Nothing
Application.CutCopyMode = False
End With

BS is a worksheet, there is no need to cycle through the range, and the
first find does nothing on its found cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"chris: dismiss 1st two postings"
wrote in message ...
lets try this again:
Find and FindNext return Range objects:
FindNext uses the Find Range object to execute:

Sub Macro6()
For Each cell In Range("BS")
Set x = Cells.Find(What:="=sumif", After:=ActiveCell)
x.Address.Activate
Set x = Cells.FindNext(x)
x.Address.Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
ActiveSheet.Paste
Application.CutCopyMode = False
Next cell
End Sub

Thank you very much for your responses. The code works and will save m
a lot of time.

Denni

--
Message posted from http://www.ExcelForum.com