Solved: Looping through Worksheets and inserting a formula into a Range of Cells

[nlmorley]

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:

[VBA]
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
[/VBA]
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.

[shrivallabha]

Can you post sample workbooks as it seems that you are using at least two workbooks?

[mdmackillop]

Welcome to VBAX.
Your code will fail if no search item is found on a sheet. There are also issues in the way you are trying to create the range. There seems no benefit here to creating a range from all the cells, so just use the standard FindNext routine

[vba]
Option Explicit
Sub InsertAreaValues()
Dim C As Range
Dim FirstAddress As String
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
FirstAddress = ""
With wSheet.Cells
Set C = .Find(what:="Area [m²]", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.Offset(0, 1).Formula = "=VLOOKUP(--$D$2,'project information'!$J$7:$O$43,6,FALSE)"
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
Next wSheet
End Sub
[/vba]

[nlmorley]

Thankyou so much - that worked perfectly! This has been bugging me for days.

I am aiming to improve my VBA programming - is there a textbook you can recommend?

Thanks again!

[shrivallabha]

This thread I've copied and pasted on a Word document for my reference. Book names with expert reviews...priceless

http://www.vbaexpress.com/forum/showthread.php?t=4557