nlmorley
06-17-2010, 09:57 PM
Hi.
I am trying to write a macro that loops through all of the sheets in a workbook.
Each time I use the Find function to find cells that contain the string "Area[m2]", and then store these values in a Range.
I then want to insert a formula into the cells next to each of the cells in the range. Unfortunately I keep getting:
Run-time error '91':
Object variable or With block variable not set
My code is:
Sub InsertAreaValues()
Dim AreaCells As Range
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
wSheet.Activate
With ActiveSheet.Cells
Set AreaCells = .Find(what:="Area [mē]", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
AreaCells.Offset(0, 1).Formula = "=VLOOKUP(--$D$2,'project information'!$J$7:$O$43,6,FALSE)"
End With
On Error Resume Next
Next wSheet
End Sub
I can't work out what I'm doing wrong. Any suggestions/help would be greatly appreciated. Perhaps there is a problem with how I am implementing the Set function, but honestly I am stuck.
Cheers.
I am trying to write a macro that loops through all of the sheets in a workbook.
Each time I use the Find function to find cells that contain the string "Area[m2]", and then store these values in a Range.
I then want to insert a formula into the cells next to each of the cells in the range. Unfortunately I keep getting:
Run-time error '91':
Object variable or With block variable not set
My code is:
Sub InsertAreaValues()
Dim AreaCells As Range
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
wSheet.Activate
With ActiveSheet.Cells
Set AreaCells = .Find(what:="Area [mē]", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
AreaCells.Offset(0, 1).Formula = "=VLOOKUP(--$D$2,'project information'!$J$7:$O$43,6,FALSE)"
End With
On Error Resume Next
Next wSheet
End Sub
I can't work out what I'm doing wrong. Any suggestions/help would be greatly appreciated. Perhaps there is a problem with how I am implementing the Set function, but honestly I am stuck.
Cheers.