Update formula in Multi sheets

[Newbie]

Excel 2003
I have the spreadsheet that has 50 sheets. I now want to add a formula to
the same cell in each sheet.
The procedure I have is as follows but it doesn't work - it doesn't add the
formula to the cell:

Any ideas?

Sub UpdateDesc()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect
Rows("5:5").RowHeight = 18.75
Range("G5").Select
ActiveCell.Formula =
"=IF(VLOOKUP(B4,Contracts!$A$1:$E$168,5,FALSE)=0,"",VLOOKUP(B4,Contracts!$A$1:$E$168,5,FALSE))"
wks.EnableSelection = xlUnlockedCells
wks.Protect
End If
Next

End Sub

Thanks

 

[Jan Karel Pieterse]

Hi Newbie,

Sub UpdateDesc()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect
Rows("5:5").RowHeight = 18.75
Range("G5").Select
ActiveCell.Formula =
"=IF(VLOOKUP(B4,Contracts!$A$1:$E$168,5,FALSE)=0,"",VLOOKUP(B4,Contract
s!$A$1:$E$168,5,FALSE))"
wks.EnableSelection = xlUnlockedCells
wks.Protect
End If
Next

End Sub

Your code references the active worksheet, precede every range object
with the worksheet object:

Sub UpdateDesc()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect
wks.Rows("5:5").RowHeight = 18.75
wks.Range("G5").Formula =
"=IF(VLOOKUP(B4,Contracts!$A$1:$E$168,5,FALSE)=0,"",VLOOKUP(B4,Contract
s!$A$1:$E$168,5,FALSE))"
wks.EnableSelection = xlUnlockedCells
wks.Protect
End If
Next

End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Newbie]

Thanks but this still doesn't work. It changes the row height but doesn't
add the formula

Any other ideas?

 

[Newbie]

I've fixed it . for some reason it needed """" (2 pairs of double quotes)
to give a blank cell if nothing found

"=IF(VLOOKUP(B4,Contracts!$A$1:$E$168,5,FALSE)=0,"""",VLOOKUP(B4,Contract