Update formula in Multi sheets
 

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

Update formula in Multi sheets
 

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

Update formula in Multi sheets
 

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

Any other ideas?

"Jan Karel Pieterse" wrote in message
...
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

Update formula in Multi sheets
 

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
s!$A$1:$E$168,5,FALSE))"


"Newbie" wrote in message
...
Thanks but this still doesn't work. It changes the row height but doesn't
add the formula

Any other ideas?

"Jan Karel Pieterse" wrote in message
...
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