Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update vlookup formula sheet reference for multiple sheets | Excel Worksheet Functions | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
Auto-update with multi-users | Excel Worksheet Functions | |||
Do..Loop in multi sheets | Excel Programming | |||
Sum Array Formula Across Multi Sheets | Excel Programming |