Object Variable or With Block Variable Not Set

[Dennis]

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

 

[Guest]

find is a range object

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[den4673]

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

Denni