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

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
>
>