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

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