Looping through worksheets question

[forgodsake8]

Hi,

I hope someone can help with this. I am trying to write a macro in Excel via Visual Basic and have hit a wall. I have a workbook with about 80 worksheets. I want to select a formula from sheet 1 (Index), and copy and paste it into certain places on evrey worksheet.

The cell where the formula needs to go is different on each worksheet, there are two result lines in each worksheet and the formula is pasted three times in along the lines.

My code (so far) will search through one sheet and replace the formula in the places I need it, however when I add code to tell it to do this in every sheet, it comes up with:

Run-time error '91':
Object variable or With block variable not set.

It doesn't seem to like the cell.find....

I have attached code below, does anyone have any ideas?

Thanks

[VBA]
Sub_Find_and_Replace()
'
' Find_and_Replace Macro
' Macro recorded 31/05/2007
'
Dim Current As Worksheet

' Copy formula
Sheets("Index").Select
Range("S1").Select
Selection.Copy


' Select sheets, search for Results and paste formula

For Each Current In Worksheets

Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Activate

ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste

ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste

ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste

Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste

ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste

ActiveCell.Offset(0, 3).Select
ActiveSheet.Paste


Next

End Sub[/VBA]

[Bob Phillips]

[vba]

Sub Find_and_Replace()
'
' Find_and_Replace Macro
' Macro recorded 31/05/2007
'
Dim Current As Worksheet
Dim oCell As Range
Dim firstAddress As String
Dim sSaved


' Copy formula
sSaved = Worksheets("Index").Range("S1")

' Select sheets, search for Results and paste formula
For Each Current In Worksheets

With Current.Cells
Set oCell = .Find(What:="Result", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)

If Not oCell Is Nothing Then
firstAddress = oCell.Address
oCell.Offset(0, 3).Value = sSaved
Do
Set oCell = .FindNext(After:=oCell)
If Not oCell Is Nothing Then
If oCell.Address <> firstAddress Then
oCell.Offset(0, 3) = sSaved
End If
End If
Loop Until oCell Is Nothing Or firstAddress = oCell.Address
End If
End With
Next Current

End Sub
[/vba]

[unmarkedhelicopter]

Alternatively you could just select the first sheet, shift click the last sheet, select the cell and Type it in once !

[mdmackillop]

Hi FGC,
Welcome to VBAX,
FYI if you select your code and select the VBA button, rather than "code", it will format as shown.
Regards
MD

[Charlize]

Alternatively you could just select the first sheet, shift click the last sheet, select the cell and Type it in once !

The cell where the formula needs to go is different on each worksheet, there are two result lines in each worksheet and the formula is pasted three times in along the lines.