forgodsake8
05-31-2007, 06:05 AM
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
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
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
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