Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
almost there ... code modify help
Hello -
I'm trying to modify some code I got from this Group and can't quite seem to make it work ... hopefully, someone can help me out.... The code is supposed to -- go to each specified sheet, and value-out links in light yellow cells, replacing #N/A with "". The code worked fine before I tried to make it unprotect each sheet and replace the #N/ A's. I'd also like the code to ONLY re-protect sheets that were originally protected -- the code currently doesn't address this need at all. Sub AABB() Dim i As Long Dim sh As Worksheet Dim rng As Range vArr = Array("P&L Summary", "P&L Acct Detail", "SALES", "FC Scenarios", "CALCRENTALEQUP", "Capital Request 07", "Capital Request 08", "CALCINV", "GPR") For i = LBound(vArr) To UBound(vArr) Set sh = Worksheets(vArr(i)) sh.Unprotect Password:="busnav" Set rng = sh.UsedRange rng.Select For Each Cell In Selection Select Case Cell.Interior.ColorIndex Case 36 Cell.Value = Cell.Value End Select Next Cell sh.UsedRange.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Range("A1").Select sh.Protect Password:="busnav", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True Next End Sub TIA for any help you can provide .... Rgds, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
almost there ... code modify help
I added an activate statement to get it to run
Sub AABB() Dim i As Long Dim sh As Worksheet Dim rng As Range vArr = Array("P&L Summary", "P&L Acct Detail", "SALES", "FC Scenarios", _ "CALCRENTALEQUP", "Capital Request 07", "Capital Request 08", "CALCINV", "GPR") For i = LBound(vArr) To UBound(vArr) Set sh = Worksheets(vArr(i)) sh.Unprotect Password:="busnav" Worksheets(vArr(i)).Activate Set rng = sh.UsedRange rng.Select For Each Cell In Selection Select Case Cell.Interior.ColorIndex Case 36 Cell.Value = Cell.Value End Select Next Cell sh.UsedRange.Replace What:="#N/A", Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Range("A1").Select sh.Protect Password:="busnav", DrawingObjects:=True, _ Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True Next End Sub "Ray" wrote: Hello - I'm trying to modify some code I got from this Group and can't quite seem to make it work ... hopefully, someone can help me out.... The code is supposed to -- go to each specified sheet, and value-out links in light yellow cells, replacing #N/A with "". The code worked fine before I tried to make it unprotect each sheet and replace the #N/ A's. I'd also like the code to ONLY re-protect sheets that were originally protected -- the code currently doesn't address this need at all. Sub AABB() Dim i As Long Dim sh As Worksheet Dim rng As Range vArr = Array("P&L Summary", "P&L Acct Detail", "SALES", "FC Scenarios", "CALCRENTALEQUP", "Capital Request 07", "Capital Request 08", "CALCINV", "GPR") For i = LBound(vArr) To UBound(vArr) Set sh = Worksheets(vArr(i)) sh.Unprotect Password:="busnav" Set rng = sh.UsedRange rng.Select For Each Cell In Selection Select Case Cell.Interior.ColorIndex Case 36 Cell.Value = Cell.Value End Select Next Cell sh.UsedRange.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Range("A1").Select sh.Protect Password:="busnav", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True Next End Sub TIA for any help you can provide .... Rgds, Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify code | Excel Discussion (Misc queries) | |||
Modify Code | Excel Worksheet Functions | |||
Modify code in UDF | Excel Programming | |||
Help me modify this VBA code please: | Excel Programming | |||
How to modify VBA code for Add-in? | Excel Programming |