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?

"Jan Karel Pieterse" <jkpieterse@netscape.net> wrote in message
news:VA.00000591.04038d58@netscape.net...
> 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]

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" <nospam@noidea.com> wrote in message
news:%23b4boI7tGHA.3392@TK2MSFTNGP04.phx.gbl...
> Thanks but this still doesn't work. It changes the row height but doesn't
> add the formula
>
> Any other ideas?
>
> "Jan Karel Pieterse" <jkpieterse@netscape.net> wrote in message
> news:VA.00000591.04038d58@netscape.net...
>> 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
>>
>>
>>
>
>