How do I trace which cell a #REF! error pointed to?

I think the only way to find out what it used to point at is by looking at a
backup copy (that hasn't been broken).

Dan E wrote:
>
> I maintain some spreadsheets containing formulae like this:-
>
> =IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).
>
> The cells containing the formulae are locked, and protection is normally
> turned on. Most of the time, the spreadsheets work fine, but occasionally,
> the user reports a #REF! error, and when I take a look, sure enough, somehow
> one of these formulae has been replaced by a version that must (I guess) be
> pointing to a non-valid cell. The error version appears like this -
>
> =IF(OR(#REF!="",#REF!="-"),"-",IF(RIGHT(#REF!,2)="OR","-",LOOKUP(#REF!,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).
>
> Is there a way to find out what the #REF! was pointing to (in the case
> above, it should be AN137)? Seems to be the only way I'm going to find out
> why this happens. The sheet has a macro, that simply does some some
> formatting and does some conditional testing on Either running a macro is
> the cause, or it may be that something happens when the sheet is temporarily
> unlocked.
>
> All help or suggestions gratefully received and acknowledged!
>
> TIA,
>
> Dan
>
> --
> Dan E
> webbie(removethis)@preferredcountry.com

--

Dave Peterson

I maintain some spreadsheets containing formulae like this:-

=IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).

The cells containing the formulae are locked, and protection is normally
turned on. Most of the time, the spreadsheets work fine, but occasionally,
the user reports a #REF! error, and when I take a look, sure enough, somehow
one of these formulae has been replaced by a version that must (I guess) be
pointing to a non-valid cell. The error version appears like this -

=IF(OR(#REF!="",#REF!="-"),"-",IF(RIGHT(#REF!,2)="OR","-",LOOKUP(#REF!,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).

Is there a way to find out what the #REF! was pointing to (in the case
above, it should be AN137)? Seems to be the only way I'm going to find out
why this happens. The sheet has a macro, that simply does some some
formatting and does some conditional testing on Either running a macro is
the cause, or it may be that something happens when the sheet is temporarily
unlocked.

All help or suggestions gratefully received and acknowledged!

TIA,

Dan

--
Dan E
webbie(removethis)@preferredcountry.com

Other than Dave's suggestion, are there perhaps similar formulas in the
cells next to it that would point you in the right direction.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
news:[email protected]...
> I maintain some spreadsheets containing formulae like this:-
>
>
=IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!
$A$1:$A$272,Crib!$B$1:$B$272))).
>
> The cells containing the formulae are locked, and protection is normally
> turned on. Most of the time, the spreadsheets work fine, but
occasionally,
> the user reports a #REF! error, and when I take a look, sure enough,
somehow
> one of these formulae has been replaced by a version that must (I guess)
be
> pointing to a non-valid cell. The error version appears like this -
>
>
=IF(OR(#REF!="",#REF!="-"),"-",IF(RIGHT(#REF!,2)="OR","-",LOOKUP(#REF!,Crib!
$A$1:$A$272,Crib!$B$1:$B$272))).
>
> Is there a way to find out what the #REF! was pointing to (in the case
> above, it should be AN137)? Seems to be the only way I'm going to find
out
> why this happens. The sheet has a macro, that simply does some some
> formatting and does some conditional testing on Either running a macro is
> the cause, or it may be that something happens when the sheet is
temporarily
> unlocked.
>
> All help or suggestions gratefully received and acknowledged!
>
> TIA,
>
> Dan
>
> --
> Dan E
> webbie(removethis)@preferredcountry.com
>
>

Dave, Ken - Fixing the error is the work of a moment, because there are,
surrounding the cell, many instances of good versions, and they're all
relative in this sheet, but I have no way of knowing how the bad version of
the formula got there. Once it's bad, it stays bad until I put a good
version in its place - I don't know of a way to wind the clock back and find
out what was there that caused the #REF!. I thought there might be some way
of going back to a point before the formula was transformed into one with
#REF!s by some action on the part of the user. I guess not, huh? I checked
an archived version, and the formula in that cell location was exactly as it
should have been. I know that something causes this, but have no clue as to
what it might be. There is a macro that the user runs, but it just does
some formatting based on schedule codes etc. - nothing I can see that could
do harm.

Any further suggestions very gladly accepted!

TIA,

Dan
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I think the only way to find out what it used to point at is by looking at
>a
> backup copy (that hasn't been broken).
>
> Dan E wrote:
>>
>> I maintain some spreadsheets containing formulae like this:-
>>
>> =IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).
>>
>> The cells containing the formulae are locked, and protection is normally
>> turned on. Most of the time, the spreadsheets work fine, but
>> occasionally,
>> the user reports a #REF! error, and when I take a look, sure enough,
>> somehow
>> one of these formulae has been replaced by a version that must (I guess)
>> be
>> pointing to a non-valid cell. The error version appears like this -
>>
>> =IF(OR(#REF!="",#REF!="-"),"-",IF(RIGHT(#REF!,2)="OR","-",LOOKUP(#REF!,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).
>>
>> Is there a way to find out what the #REF! was pointing to (in the case
>> above, it should be AN137)? Seems to be the only way I'm going to find
>> out
>> why this happens. The sheet has a macro, that simply does some some
>> formatting and does some conditional testing on Either running a macro is
>> the cause, or it may be that something happens when the sheet is
>> temporarily
>> unlocked.
>>
>> All help or suggestions gratefully received and acknowledged!
>>
>> TIA,
>>
>> Dan
>>
>> --
>> Dan E
>> webbie(removethis)@preferredcountry.com
>
> --
>
> Dave Peterson

Ken - thanks for your input - see my reply to Dave.

Dan
"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Other than Dave's suggestion, are there perhaps similar formulas in the
> cells next to it that would point you in the right direction.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
> news:[email protected]...
>> I maintain some spreadsheets containing formulae like this:-
>>
>>
> =IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!
> $A$1:$A$272,Crib!$B$1:$B$272))).
>>
>> The cells containing the formulae are locked, and protection is normally
>> turned on. Most of the time, the spreadsheets work fine, but
> occasionally,
>> the user reports a #REF! error, and when I take a look, sure enough,
> somehow
>> one of these formulae has been replaced by a version that must (I guess)
> be
>> pointing to a non-valid cell. The error version appears like this -
>>
>>
> =IF(OR(#REF!="",#REF!="-"),"-",IF(RIGHT(#REF!,2)="OR","-",LOOKUP(#REF!,Crib!
> $A$1:$A$272,Crib!$B$1:$B$272))).
>>
>> Is there a way to find out what the #REF! was pointing to (in the case
>> above, it should be AN137)? Seems to be the only way I'm going to find
> out
>> why this happens. The sheet has a macro, that simply does some some
>> formatting and does some conditional testing on Either running a macro is
>> the cause, or it may be that something happens when the sheet is
> temporarily
>> unlocked.
>>
>> All help or suggestions gratefully received and acknowledged!
>>
>> TIA,
>>
>> Dan
>>
>> --
>> Dan E
>> webbie(removethis)@preferredcountry.com
>>
>>
>
>

You generally get these errors because the cell that they referred to has
been deleted - not cleared, but physically deleted. eg if you have a formula
such as =A1+1 and you delete Col A, bingo #REF error. Check the code for
anything that looks like it will delete a range, or post it here and we can
take a look. Failing that it's probably down to a PICNIC error - (Problem
In Chair Not In Computer) - User may well be deleting cells/rows/columns
manually.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip>

Or even worksheets.

=sheet2!a1

will work nicly until sheet2 is deleted.

Ken Wright wrote:
>
> You generally get these errors because the cell that they referred to has
> been deleted - not cleared, but physically deleted. eg if you have a formula
> such as =A1+1 and you delete Col A, bingo #REF error. Check the code for
> anything that looks like it will delete a range, or post it here and we can
> take a look. Failing that it's probably down to a PICNIC error - (Problem
> In Chair Not In Computer) - User may well be deleting cells/rows/columns
> manually.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> <snip>

--

Dave Peterson

Thanks, Dave and Ken. User swears that no cell deletions have occurred, and
indeed there's no evidence that that happened. The sheet covers 2 weeks of
a schedule, and each day has 3 cells in a row (rows being a person), the 3
cells being in order - shift code, formula, formula, then that pattern
repeats. The surrounds of the bad formula show no evidence of cell, row or
column deletions, so it's weird.... The macro doesn't delete anything, just
changes attributes and/or values.

Thanks again,

Dan


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> You generally get these errors because the cell that they referred to has
> been deleted - not cleared, but physically deleted. eg if you have a
> formula
> such as =A1+1 and you delete Col A, bingo #REF error. Check the code for
> anything that looks like it will delete a range, or post it here and we
> can
> take a look. Failing that it's probably down to a PICNIC error -
> (Problem
> In Chair Not In Computer) - User may well be deleting cells/rows/columns
> manually.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> <snip>
>
>

Thanks Dave - see reply to Ken.

Dan
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Or even worksheets.
>
> =sheet2!a1
>
> will work nicly until sheet2 is deleted.
>
> Ken Wright wrote:
>>
>> You generally get these errors because the cell that they referred to has
>> been deleted - not cleared, but physically deleted. eg if you have a
>> formula
>> such as =A1+1 and you delete Col A, bingo #REF error. Check the code for
>> anything that looks like it will delete a range, or post it here and we
>> can
>> take a look. Failing that it's probably down to a PICNIC error -
>> (Problem
>> In Chair Not In Computer) - User may well be deleting cells/rows/columns
>> manually.
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ----------------------------------------------------------------------------
>> It's easier to beg forgiveness than ask permission :-)
>> ----------------------------------------------------------------------------
>>
>> <snip>
>
> --
>
> Dave Peterson

Any references to named ranges where the names may have been deleted? A lot
easier to give suggestions if we can see the code - If nothing else it helps
us be sure we can discount it.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>

Ken - no named ranges in the sheets. Here's the macro code - rather long
I'm afraid, and please remember I'm an amateur - most of this code was
suggested to me by very kind people on this newsgroup :-). It does these
jobs - 1) Colors cells containing specific shift codes; 2) Selects "active"
rows, where there's an X in column AT, and clears the color background from
empty cells in those rows; 3) Hunts for values of 1,2 or 3 in column W in
active rows (such codes denting where all shifts are covered by 2 workers,
providing continuity of care to patients), and calls subs to work out
continuity of care overtime (>40 hours per week) 4) calculates all
overtime:-
_______________
Sub Main_REHAB()
ActiveSheet.Unprotect
Color_Text
myRows
CC_OT
ALL_OT
ActiveSheet.Protect
End Sub

Sub Color_Text()
Dim cell As Range
Dim col As Integer
On Error GoTo ws_next
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 1 Or _
cell.Interior.ColorIndex = 15 Then
ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
Select Case LCase(cell.Value)
Case "umr": col = 40
Case "ra": col = 38
Case "rb": col = 35
Case "rc": col = 36
Case "cs": col = 37
Case "rf": col = 38
Case "rg": col = 35
Case "rh": col = 36
Case "r1": col = 38
Case "r2": col = 35
Case "r3": col = 36
Case "r4": col = 24
Case "r5": col = 43
Case "r6": col = 22
Case "r8": col = 38
Case "r9": col = 35
Case "r10": col = 36
Case "eto": col = 0
Case Else: col = cell.Interior.ColorIndex
End Select
cell.Interior.ColorIndex = col
End If
ws_next:
Next
ws_exit:
End Sub

Sub myRows()

Dim oRow As Range
Dim cell As Range
On Error GoTo ws_next2
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
If IsEmpty(cell.Value) Then
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
End If
ws_next2:
Next oRow

End Sub

Sub CC_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next3
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
If Cells(oRow.Row, "W").Value = "1" Then
Call week1(oRow)
ElseIf Cells(oRow.Row, "W").Value = "2" Then
Call week2(oRow)
ElseIf Cells(oRow.Row, "W").Value = "3" Then
Call bothweeks(oRow)
End If
End If
ws_next3:
Next oRow

End Sub

Sub week1(oRow As Range)
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
End Sub

Sub week2(oRow As Range)

If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
End If
End Sub

Sub bothweeks(oRow As Range)

Cells(oRow.Row, "BA").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
(Cells(oRow.Row, "AX").Value - 40))
End If
End Sub

Sub ALL_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next4
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
Cells(oRow.Row, "BB").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BB").Value = Cells(oRow.Row, "AW").Value -
40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BB").Value = (Cells(oRow.Row, "BB").Value +
_
(Cells(oRow.Row, "AX").Value - 40))
End If
End If
ws_next4:
Next oRow

End Sub




"Ken Wright" <[email protected]> wrote in message
news:%23HMx7%[email protected]...
> Any references to named ranges where the names may have been deleted? A
> lot
> easier to give suggestions if we can see the code - If nothing else it
> helps
> us be sure we can discount it.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
> <snip>
>
>

What about the formula that now includes the #ref! error?

Do the users do anything to those cells? Or near those cells?



Dan E wrote:
>
> Ken - no named ranges in the sheets. Here's the macro code - rather long
> I'm afraid, and please remember I'm an amateur - most of this code was
> suggested to me by very kind people on this newsgroup :-). It does these
> jobs - 1) Colors cells containing specific shift codes; 2) Selects "active"
> rows, where there's an X in column AT, and clears the color background from
> empty cells in those rows; 3) Hunts for values of 1,2 or 3 in column W in
> active rows (such codes denting where all shifts are covered by 2 workers,
> providing continuity of care to patients), and calls subs to work out
> continuity of care overtime (>40 hours per week) 4) calculates all
> overtime:-
> _______________
> Sub Main_REHAB()
> ActiveSheet.Unprotect
> Color_Text
> myRows
> CC_OT
> ALL_OT
> ActiveSheet.Protect
> End Sub
>
> Sub Color_Text()
> Dim cell As Range
> Dim col As Integer
> On Error GoTo ws_next
> For Each cell In ActiveSheet.UsedRange
> If cell.Interior.ColorIndex = 1 Or _
> cell.Interior.ColorIndex = 15 Then
> ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
> Select Case LCase(cell.Value)
> Case "umr": col = 40
> Case "ra": col = 38
> Case "rb": col = 35
> Case "rc": col = 36
> Case "cs": col = 37
> Case "rf": col = 38
> Case "rg": col = 35
> Case "rh": col = 36
> Case "r1": col = 38
> Case "r2": col = 35
> Case "r3": col = 36
> Case "r4": col = 24
> Case "r5": col = 43
> Case "r6": col = 22
> Case "r8": col = 38
> Case "r9": col = 35
> Case "r10": col = 36
> Case "eto": col = 0
> Case Else: col = cell.Interior.ColorIndex
> End Select
> cell.Interior.ColorIndex = col
> End If
> ws_next:
> Next
> ws_exit:
> End Sub
>
> Sub myRows()
>
> Dim oRow As Range
> Dim cell As Range
> On Error GoTo ws_next2
> For Each oRow In ActiveSheet.UsedRange.Rows
> If Cells(oRow.Row, "AT").Value = "X" Then
> For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
> If IsEmpty(cell.Value) Then
> cell.Interior.ColorIndex = xlColorIndexNone
> End If
> Next cell
> End If
> ws_next2:
> Next oRow
>
> End Sub
>
> Sub CC_OT()
>
> Dim oRow As Range
> Dim cell As Range
>
> On Error GoTo ws_next3
> For Each oRow In ActiveSheet.UsedRange.Rows
> If Cells(oRow.Row, "AT").Value = "X" Then
> If Cells(oRow.Row, "W").Value = "1" Then
> Call week1(oRow)
> ElseIf Cells(oRow.Row, "W").Value = "2" Then
> Call week2(oRow)
> ElseIf Cells(oRow.Row, "W").Value = "3" Then
> Call bothweeks(oRow)
> End If
> End If
> ws_next3:
> Next oRow
>
> End Sub
>
> Sub week1(oRow As Range)
> If Cells(oRow.Row, "AW").Value > 40 Then
> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
> End If
> End Sub
>
> Sub week2(oRow As Range)
>
> If Cells(oRow.Row, "AX").Value > 40 Then
> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
> End If
> End Sub
>
> Sub bothweeks(oRow As Range)
>
> Cells(oRow.Row, "BA").Value = 0
> If Cells(oRow.Row, "AW").Value > 40 Then
> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
> End If
> If Cells(oRow.Row, "AX").Value > 40 Then
> Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
> (Cells(oRow.Row, "AX").Value - 40))
> End If
> End Sub
>
> Sub ALL_OT()
>
> Dim oRow As Range
> Dim cell As Range
>
> On Error GoTo ws_next4
> For Each oRow In ActiveSheet.UsedRange.Rows
> If Cells(oRow.Row, "AT").Value = "X" Then
> Cells(oRow.Row, "BB").Value = 0
> If Cells(oRow.Row, "AW").Value > 40 Then
> Cells(oRow.Row, "BB").Value = Cells(oRow.Row, "AW").Value -
> 40
> End If
> If Cells(oRow.Row, "AX").Value > 40 Then
> Cells(oRow.Row, "BB").Value = (Cells(oRow.Row, "BB").Value +
> _
> (Cells(oRow.Row, "AX").Value - 40))
> End If
> End If
> ws_next4:
> Next oRow
>
> End Sub
>
> "Ken Wright" <[email protected]> wrote in message
> news:%23HMx7%[email protected]...
> > Any references to named ranges where the names may have been deleted? A
> > lot
> > easier to give suggestions if we can see the code - If nothing else it
> > helps
> > us be sure we can discount it.
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
> > ----------------------------------------------------------------------------
> > It's easier to beg forgiveness than ask permission :-)
> > ----------------------------------------------------------------------------
> > <snip>
> >
> >

--

Dave Peterson

"Ken Wright" <[email protected]> wrote...
>You generally get these errors because the cell that they referred to
>has been deleted - not cleared, but physically deleted. eg if you have
>a formula such as =A1+1 and you delete Col A, bingo #REF error. . . .
....

Not necessarily. User could also be CUTTING some other cell entirely and
pasting into the referenced cell. That'll produce #REF! too.

Dave - all they do is enter or clear a shift code one or two spaces to the
left.

Dan
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> What about the formula that now includes the #ref! error?
>
> Do the users do anything to those cells? Or near those cells?
>
>
>
> Dan E wrote:
>>
>> Ken - no named ranges in the sheets. Here's the macro code - rather long
>> I'm afraid, and please remember I'm an amateur - most of this code was
>> suggested to me by very kind people on this newsgroup :-). It does these
>> jobs - 1) Colors cells containing specific shift codes; 2) Selects
>> "active"
>> rows, where there's an X in column AT, and clears the color background
>> from
>> empty cells in those rows; 3) Hunts for values of 1,2 or 3 in column W in
>> active rows (such codes denting where all shifts are covered by 2
>> workers,
>> providing continuity of care to patients), and calls subs to work out
>> continuity of care overtime (>40 hours per week) 4) calculates all
>> overtime:-
>> _______________
>> Sub Main_REHAB()
>> ActiveSheet.Unprotect
>> Color_Text
>> myRows
>> CC_OT
>> ALL_OT
>> ActiveSheet.Protect
>> End Sub
>>
>> Sub Color_Text()
>> Dim cell As Range
>> Dim col As Integer
>> On Error GoTo ws_next
>> For Each cell In ActiveSheet.UsedRange
>> If cell.Interior.ColorIndex = 1 Or _
>> cell.Interior.ColorIndex = 15 Then
>> ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
>> Select Case LCase(cell.Value)
>> Case "umr": col = 40
>> Case "ra": col = 38
>> Case "rb": col = 35
>> Case "rc": col = 36
>> Case "cs": col = 37
>> Case "rf": col = 38
>> Case "rg": col = 35
>> Case "rh": col = 36
>> Case "r1": col = 38
>> Case "r2": col = 35
>> Case "r3": col = 36
>> Case "r4": col = 24
>> Case "r5": col = 43
>> Case "r6": col = 22
>> Case "r8": col = 38
>> Case "r9": col = 35
>> Case "r10": col = 36
>> Case "eto": col = 0
>> Case Else: col = cell.Interior.ColorIndex
>> End Select
>> cell.Interior.ColorIndex = col
>> End If
>> ws_next:
>> Next
>> ws_exit:
>> End Sub
>>
>> Sub myRows()
>>
>> Dim oRow As Range
>> Dim cell As Range
>> On Error GoTo ws_next2
>> For Each oRow In ActiveSheet.UsedRange.Rows
>> If Cells(oRow.Row, "AT").Value = "X" Then
>> For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
>> If IsEmpty(cell.Value) Then
>> cell.Interior.ColorIndex = xlColorIndexNone
>> End If
>> Next cell
>> End If
>> ws_next2:
>> Next oRow
>>
>> End Sub
>>
>> Sub CC_OT()
>>
>> Dim oRow As Range
>> Dim cell As Range
>>
>> On Error GoTo ws_next3
>> For Each oRow In ActiveSheet.UsedRange.Rows
>> If Cells(oRow.Row, "AT").Value = "X" Then
>> If Cells(oRow.Row, "W").Value = "1" Then
>> Call week1(oRow)
>> ElseIf Cells(oRow.Row, "W").Value = "2" Then
>> Call week2(oRow)
>> ElseIf Cells(oRow.Row, "W").Value = "3" Then
>> Call bothweeks(oRow)
>> End If
>> End If
>> ws_next3:
>> Next oRow
>>
>> End Sub
>>
>> Sub week1(oRow As Range)
>> If Cells(oRow.Row, "AW").Value > 40 Then
>> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
>> End If
>> End Sub
>>
>> Sub week2(oRow As Range)
>>
>> If Cells(oRow.Row, "AX").Value > 40 Then
>> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
>> End If
>> End Sub
>>
>> Sub bothweeks(oRow As Range)
>>
>> Cells(oRow.Row, "BA").Value = 0
>> If Cells(oRow.Row, "AW").Value > 40 Then
>> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
>> End If
>> If Cells(oRow.Row, "AX").Value > 40 Then
>> Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
>> (Cells(oRow.Row, "AX").Value - 40))
>> End If
>> End Sub
>>
>> Sub ALL_OT()
>>
>> Dim oRow As Range
>> Dim cell As Range
>>
>> On Error GoTo ws_next4
>> For Each oRow In ActiveSheet.UsedRange.Rows
>> If Cells(oRow.Row, "AT").Value = "X" Then
>> Cells(oRow.Row, "BB").Value = 0
>> If Cells(oRow.Row, "AW").Value > 40 Then
>> Cells(oRow.Row, "BB").Value = Cells(oRow.Row,
>> "AW").Value -
>> 40
>> End If
>> If Cells(oRow.Row, "AX").Value > 40 Then
>> Cells(oRow.Row, "BB").Value = (Cells(oRow.Row,
>> "BB").Value +
>> _
>> (Cells(oRow.Row, "AX").Value - 40))
>> End If
>> End If
>> ws_next4:
>> Next oRow
>>
>> End Sub
>>
>> "Ken Wright" <[email protected]> wrote in message
>> news:%23HMx7%[email protected]...
>> > Any references to named ranges where the names may have been deleted?
>> > A
>> > lot
>> > easier to give suggestions if we can see the code - If nothing else it
>> > helps
>> > us be sure we can discount it.
>> >
>> > --
>> > Regards
>> > Ken....................... Microsoft MVP - Excel
>> > Sys Spec - Win XP Pro / XL 97/00/02/03
>> >
>> > ----------------------------------------------------------------------------
>> > It's easier to beg forgiveness than ask permission :-)
>> > ----------------------------------------------------------------------------
>> > <snip>
>> >
>> >
>
> --
>
> Dave Peterson

Thanks, Harlan - good point.

Dan
"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Ken Wright" <[email protected]> wrote...
>>You generally get these errors because the cell that they referred to
>>has been deleted - not cleared, but physically deleted. eg if you have
>>a formula such as =A1+1 and you delete Col A, bingo #REF error. . . .
> ...
>
> Not necessarily. User could also be CUTTING some other cell entirely and
> pasting into the referenced cell. That'll produce #REF! too.
>
>

Very true - Had forgotten that.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Ken Wright" <[email protected]> wrote...
> >You generally get these errors because the cell that they referred to
> >has been deleted - not cleared, but physically deleted. eg if you have
> >a formula such as =A1+1 and you delete Col A, bingo #REF error. . . .
> ...
>
> Not necessarily. User could also be CUTTING some other cell entirely and
> pasting into the referenced cell. That'll produce #REF! too.
>
>

To be honest, discounting the macro should be a doddle, and there's nothing
I can see that immediately springs to mind, but just run it on a working
copy of the sheet and you'll soon see if it was that in any way. As soon as
you discount that, then whatever is left, no matter how improbable, or how
much they deny it.............. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
news:%[email protected]...
> Dave - all they do is enter or clear a shift code one or two spaces to the
> left.
>
> Dan
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > What about the formula that now includes the #ref! error?
> >
> > Do the users do anything to those cells? Or near those cells?
> >
> >
> >
> > Dan E wrote:
> >>
> >> Ken - no named ranges in the sheets. Here's the macro code - rather
long
> >> I'm afraid, and please remember I'm an amateur - most of this code was
> >> suggested to me by very kind people on this newsgroup :-). It does
these
> >> jobs - 1) Colors cells containing specific shift codes; 2) Selects
> >> "active"
> >> rows, where there's an X in column AT, and clears the color background
> >> from
> >> empty cells in those rows; 3) Hunts for values of 1,2 or 3 in column W
in
> >> active rows (such codes denting where all shifts are covered by 2
> >> workers,
> >> providing continuity of care to patients), and calls subs to work out
> >> continuity of care overtime (>40 hours per week) 4) calculates all
> >> overtime:-
> >> _______________
> >> Sub Main_REHAB()
> >> ActiveSheet.Unprotect
> >> Color_Text
> >> myRows
> >> CC_OT
> >> ALL_OT
> >> ActiveSheet.Protect
> >> End Sub
> >>
> >> Sub Color_Text()
> >> Dim cell As Range
> >> Dim col As Integer
> >> On Error GoTo ws_next
> >> For Each cell In ActiveSheet.UsedRange
> >> If cell.Interior.ColorIndex = 1 Or _
> >> cell.Interior.ColorIndex = 15 Then
> >> ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
> >> Select Case LCase(cell.Value)
> >> Case "umr": col = 40
> >> Case "ra": col = 38
> >> Case "rb": col = 35
> >> Case "rc": col = 36
> >> Case "cs": col = 37
> >> Case "rf": col = 38
> >> Case "rg": col = 35
> >> Case "rh": col = 36
> >> Case "r1": col = 38
> >> Case "r2": col = 35
> >> Case "r3": col = 36
> >> Case "r4": col = 24
> >> Case "r5": col = 43
> >> Case "r6": col = 22
> >> Case "r8": col = 38
> >> Case "r9": col = 35
> >> Case "r10": col = 36
> >> Case "eto": col = 0
> >> Case Else: col = cell.Interior.ColorIndex
> >> End Select
> >> cell.Interior.ColorIndex = col
> >> End If
> >> ws_next:
> >> Next
> >> ws_exit:
> >> End Sub
> >>
> >> Sub myRows()
> >>
> >> Dim oRow As Range
> >> Dim cell As Range
> >> On Error GoTo ws_next2
> >> For Each oRow In ActiveSheet.UsedRange.Rows
> >> If Cells(oRow.Row, "AT").Value = "X" Then
> >> For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
> >> If IsEmpty(cell.Value) Then
> >> cell.Interior.ColorIndex = xlColorIndexNone
> >> End If
> >> Next cell
> >> End If
> >> ws_next2:
> >> Next oRow
> >>
> >> End Sub
> >>
> >> Sub CC_OT()
> >>
> >> Dim oRow As Range
> >> Dim cell As Range
> >>
> >> On Error GoTo ws_next3
> >> For Each oRow In ActiveSheet.UsedRange.Rows
> >> If Cells(oRow.Row, "AT").Value = "X" Then
> >> If Cells(oRow.Row, "W").Value = "1" Then
> >> Call week1(oRow)
> >> ElseIf Cells(oRow.Row, "W").Value = "2" Then
> >> Call week2(oRow)
> >> ElseIf Cells(oRow.Row, "W").Value = "3" Then
> >> Call bothweeks(oRow)
> >> End If
> >> End If
> >> ws_next3:
> >> Next oRow
> >>
> >> End Sub
> >>
> >> Sub week1(oRow As Range)
> >> If Cells(oRow.Row, "AW").Value > 40 Then
> >> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
> >> End If
> >> End Sub
> >>
> >> Sub week2(oRow As Range)
> >>
> >> If Cells(oRow.Row, "AX").Value > 40 Then
> >> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
> >> End If
> >> End Sub
> >>
> >> Sub bothweeks(oRow As Range)
> >>
> >> Cells(oRow.Row, "BA").Value = 0
> >> If Cells(oRow.Row, "AW").Value > 40 Then
> >> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
> >> End If
> >> If Cells(oRow.Row, "AX").Value > 40 Then
> >> Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
> >> (Cells(oRow.Row, "AX").Value - 40))
> >> End If
> >> End Sub
> >>
> >> Sub ALL_OT()
> >>
> >> Dim oRow As Range
> >> Dim cell As Range
> >>
> >> On Error GoTo ws_next4
> >> For Each oRow In ActiveSheet.UsedRange.Rows
> >> If Cells(oRow.Row, "AT").Value = "X" Then
> >> Cells(oRow.Row, "BB").Value = 0
> >> If Cells(oRow.Row, "AW").Value > 40 Then
> >> Cells(oRow.Row, "BB").Value = Cells(oRow.Row,
> >> "AW").Value -
> >> 40
> >> End If
> >> If Cells(oRow.Row, "AX").Value > 40 Then
> >> Cells(oRow.Row, "BB").Value = (Cells(oRow.Row,
> >> "BB").Value +
> >> _
> >> (Cells(oRow.Row, "AX").Value - 40))
> >> End If
> >> End If
> >> ws_next4:
> >> Next oRow
> >>
> >> End Sub
> >>
> >> "Ken Wright" <[email protected]> wrote in message
> >> news:%23HMx7%[email protected]...
> >> > Any references to named ranges where the names may have been deleted?
> >> > A
> >> > lot
> >> > easier to give suggestions if we can see the code - If nothing else
it
> >> > helps
> >> > us be sure we can discount it.
> >> >
> >> > --
> >> > Regards
> >> > Ken....................... Microsoft MVP - Excel
> >> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >> >
> >>
> --------------------------------------------------------------------------
--
> >> > It's easier to beg forgiveness than ask permission
:-)
> >>
> --------------------------------------------------------------------------
--
> >> > <snip>
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson
>
>

<snip>

Before I forget again :-)

Thanks, Ken - your inevitable conclusion reminds me of Poirot (or was it
Miss Marple, or maybe Holmes).... :-)

Thanks again.

Dan
"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> To be honest, discounting the macro should be a doddle, and there's
> nothing
> I can see that immediately springs to mind, but just run it on a working
> copy of the sheet and you'll soon see if it was that in any way. As soon
> as
> you discount that, then whatever is left, no matter how improbable, or how
> much they deny it.............. :-)
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
> news:%[email protected]...
>> Dave - all they do is enter or clear a shift code one or two spaces to
>> the
>> left.
>>
>> Dan
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> > What about the formula that now includes the #ref! error?
>> >
>> > Do the users do anything to those cells? Or near those cells?
>> >
>> >
>> >
>> > Dan E wrote:
>> >>
>> >> Ken - no named ranges in the sheets. Here's the macro code - rather
> long
>> >> I'm afraid, and please remember I'm an amateur - most of this code was
>> >> suggested to me by very kind people on this newsgroup :-). It does
> these
>> >> jobs - 1) Colors cells containing specific shift codes; 2) Selects
>> >> "active"
>> >> rows, where there's an X in column AT, and clears the color background
>> >> from
>> >> empty cells in those rows; 3) Hunts for values of 1,2 or 3 in column W
> in
>> >> active rows (such codes denting where all shifts are covered by 2
>> >> workers,
>> >> providing continuity of care to patients), and calls subs to work out
>> >> continuity of care overtime (>40 hours per week) 4) calculates all
>> >> overtime:-
>> >> _______________
>> >> Sub Main_REHAB()
>> >> ActiveSheet.Unprotect
>> >> Color_Text
>> >> myRows
>> >> CC_OT
>> >> ALL_OT
>> >> ActiveSheet.Protect
>> >> End Sub
>> >>
>> >> Sub Color_Text()
>> >> Dim cell As Range
>> >> Dim col As Integer
>> >> On Error GoTo ws_next
>> >> For Each cell In ActiveSheet.UsedRange
>> >> If cell.Interior.ColorIndex = 1 Or _
>> >> cell.Interior.ColorIndex = 15 Then
>> >> ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
>> >> Select Case LCase(cell.Value)
>> >> Case "umr": col = 40
>> >> Case "ra": col = 38
>> >> Case "rb": col = 35
>> >> Case "rc": col = 36
>> >> Case "cs": col = 37
>> >> Case "rf": col = 38
>> >> Case "rg": col = 35
>> >> Case "rh": col = 36
>> >> Case "r1": col = 38
>> >> Case "r2": col = 35
>> >> Case "r3": col = 36
>> >> Case "r4": col = 24
>> >> Case "r5": col = 43
>> >> Case "r6": col = 22
>> >> Case "r8": col = 38
>> >> Case "r9": col = 35
>> >> Case "r10": col = 36
>> >> Case "eto": col = 0
>> >> Case Else: col = cell.Interior.ColorIndex
>> >> End Select
>> >> cell.Interior.ColorIndex = col
>> >> End If
>> >> ws_next:
>> >> Next
>> >> ws_exit:
>> >> End Sub
>> >>
>> >> Sub myRows()
>> >>
>> >> Dim oRow As Range
>> >> Dim cell As Range
>> >> On Error GoTo ws_next2
>> >> For Each oRow In ActiveSheet.UsedRange.Rows
>> >> If Cells(oRow.Row, "AT").Value = "X" Then
>> >> For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
>> >> If IsEmpty(cell.Value) Then
>> >> cell.Interior.ColorIndex = xlColorIndexNone
>> >> End If
>> >> Next cell
>> >> End If
>> >> ws_next2:
>> >> Next oRow
>> >>
>> >> End Sub
>> >>
>> >> Sub CC_OT()
>> >>
>> >> Dim oRow As Range
>> >> Dim cell As Range
>> >>
>> >> On Error GoTo ws_next3
>> >> For Each oRow In ActiveSheet.UsedRange.Rows
>> >> If Cells(oRow.Row, "AT").Value = "X" Then
>> >> If Cells(oRow.Row, "W").Value = "1" Then
>> >> Call week1(oRow)
>> >> ElseIf Cells(oRow.Row, "W").Value = "2" Then
>> >> Call week2(oRow)
>> >> ElseIf Cells(oRow.Row, "W").Value = "3" Then
>> >> Call bothweeks(oRow)
>> >> End If
>> >> End If
>> >> ws_next3:
>> >> Next oRow
>> >>
>> >> End Sub
>> >>
>> >> Sub week1(oRow As Range)
>> >> If Cells(oRow.Row, "AW").Value > 40 Then
>> >> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
>> >> End If
>> >> End Sub
>> >>
>> >> Sub week2(oRow As Range)
>> >>
>> >> If Cells(oRow.Row, "AX").Value > 40 Then
>> >> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
>> >> End If
>> >> End Sub
>> >>
>> >> Sub bothweeks(oRow As Range)
>> >>
>> >> Cells(oRow.Row, "BA").Value = 0
>> >> If Cells(oRow.Row, "AW").Value > 40 Then
>> >> Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
>> >> End If
>> >> If Cells(oRow.Row, "AX").Value > 40 Then
>> >> Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
>> >> (Cells(oRow.Row, "AX").Value - 40))
>> >> End If
>> >> End Sub
>> >>
>> >> Sub ALL_OT()
>> >>
>> >> Dim oRow As Range
>> >> Dim cell As Range
>> >>
>> >> On Error GoTo ws_next4
>> >> For Each oRow In ActiveSheet.UsedRange.Rows
>> >> If Cells(oRow.Row, "AT").Value = "X" Then
>> >> Cells(oRow.Row, "BB").Value = 0
>> >> If Cells(oRow.Row, "AW").Value > 40 Then
>> >> Cells(oRow.Row, "BB").Value = Cells(oRow.Row,
>> >> "AW").Value -
>> >> 40
>> >> End If
>> >> If Cells(oRow.Row, "AX").Value > 40 Then
>> >> Cells(oRow.Row, "BB").Value = (Cells(oRow.Row,
>> >> "BB").Value +
>> >> _
>> >> (Cells(oRow.Row, "AX").Value - 40))
>> >> End If
>> >> End If
>> >> ws_next4:
>> >> Next oRow
>> >>
>> >> End Sub
>> >>
>> >> "Ken Wright" <[email protected]> wrote in message
>> >> news:%23HMx7%[email protected]...
>> >> > Any references to named ranges where the names may have been
>> >> > deleted?
>> >> > A
>> >> > lot
>> >> > easier to give suggestions if we can see the code - If nothing else
> it
>> >> > helps
>> >> > us be sure we can discount it.
>> >> >
>> >> > --
>> >> > Regards
>> >> > Ken....................... Microsoft MVP - Excel
>> >> > Sys Spec - Win XP Pro / XL 97/00/02/03
>> >> >
>> >>
>> --------------------------------------------------------------------------
> --
>> >> > It's easier to beg forgiveness than ask permission
> :-)
>> >>
>> --------------------------------------------------------------------------
> --
>> >> > <snip>
>> >> >
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>>
>>
>
>

LOL - You just listed 3 of my favourite programs, Poirot in fact being my
all time favourite.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
news:[email protected]...
> Thanks, Ken - your inevitable conclusion reminds me of Poirot (or was it
> Miss Marple, or maybe Holmes).... :-)
>
> Thanks again.
>
> Dan
> "Ken Wright" <[email protected]> wrote in message
<snip>

Joan Hickson's Miss Marple is top of my list - wish there were more! How
dare she die!

Dan

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> LOL - You just listed 3 of my favourite programs, Poirot in fact being my
> all time favourite.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
> news:[email protected]...
>> Thanks, Ken - your inevitable conclusion reminds me of Poirot (or was it
>> Miss Marple, or maybe Holmes).... :-)
>>
>> Thanks again.
>>
>> Dan
>> "Ken Wright" <[email protected]> wrote in message
> <snip>
>
>

Hmmm - was a tight call, but Poirot just edged it for me. She came a damn
close second for me though :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
news:[email protected]...
> Joan Hickson's Miss Marple is top of my list - wish there were more! How
> dare she die!
>
> Dan
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> > LOL - You just listed 3 of my favourite programs, Poirot in fact being
my
> > all time favourite.
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
--
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
--
> >
> > "Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
> > news:[email protected]...
> >> Thanks, Ken - your inevitable conclusion reminds me of Poirot (or was
it
> >> Miss Marple, or maybe Holmes).... :-)
> >>
> >> Thanks again.
> >>
> >> Dan
> >> "Ken Wright" <[email protected]> wrote in message
> > <snip>
> >
> >
>
>