Code for dealing with #REF

[Kevin Porter]

I have some fields that end up #REF because of deletes that are done. I
would like to look at that field, if it contains #REF then I want that field
to be the sum of all the columns in that row. If it doesn't then I want it
to equal what it equals.

Thanks,

Kevin Porter

 

[Jim Rech]

You can trap REFs or any kind of errors in cell values like this:

=IF(ISERROR(SUM(A1:A10)),0,SUM(A1:A10))

Excel 2007 has a shorter form:

=IFERROR(SUM(A1:A10),0)

You'd of course replace the 0 with another formula.


--
Jim
|I have some fields that end up #REF because of deletes that are done. I
| would like to look at that field, if it contains #REF then I want that
field
| to be the sum of all the columns in that row. If it doesn't then I want
it
| to equal what it equals.
|
| Thanks,
|
| Kevin Porter

 

[Kevin Porter]

How would I use it when using variables. I am using RowToFind and ColToFind
earlier in the code. But when I try to replace (A1:A10) in the code with
those variables it does not recognize them, of course. I have been playing
with it since your response and not making much headway.

Thanks for your help.

 

[Jim Rech]

I gave you a worksheet formula because it wasn't clear to me whether you
were writing code or writing formulas.

While I'm still not sure what you're doing, you can check a cell like this
for an error value:

If IsError(Cells(RowToFind, ColToFind).Value) Then
MsgBox "Error"
End If

or like this:

If IsError(Range("C9").Value) Then
MsgBox "Error"
End If

Or with its range name if you've assigned one.

--
Jim
| How would I use it when using variables. I am using RowToFind and
ColToFind
| earlier in the code. But when I try to replace (A1:A10) in the code with
| those variables it does not recognize them, of course. I have been
playing
| with it since your response and not making much headway.
|
| Thanks for your help.
|
| "Jim Rech" wrote:
|
| > You can trap REFs or any kind of errors in cell values like this:
| >
| > =IF(ISERROR(SUM(A1:A10)),0,SUM(A1:A10))
| >
| > Excel 2007 has a shorter form:
| >
| > =IFERROR(SUM(A1:A10),0)
| >
| > You'd of course replace the 0 with another formula.
| >
| >
| > --
| > Jim
| > | > |I have some fields that end up #REF because of deletes that are done.
I
| > | would like to look at that field, if it contains #REF then I want that
| > field
| > | to be the sum of all the columns in that row. If it doesn't then I
want
| > it
| > | to equal what it equals.
| > |
| > | Thanks,
| > |
| > | Kevin Porter
| >
| >
| >

 

[Kevin Porter]

Yes, reading back over it, it is a bit obscure. Let's try again, because the
second one is close to what I need.

I am writing code that looks at a specific cell (RowToFind,ColToFind) and
checks if it has an error condition, in this case I know it will be a #REF
error. If it is errored then I want to sum all cells from (RowToFind, 5) to
(RowToFind, ColToFind - 1) and write that number into (RowToFind, ColToFind).
The following is the code I tried:

If IsError(Cells(RowToFind, ColToFind)) Then Workbooks("SRA Detailed Payroll
- " & WBDate & ".xls").Sheets("Shreveport").Cells(RowToFind, ColToFind) =
"=Sum(cells(RowToFind, 5):cells(rowtofind, coltofind[-1]))"


I think I am ok up to the =Sum command.

Thanks again for your help and patience.

 

[Jim Rech]

I think the end of it should go like this:

..Cells(RowToFind, ColToFind) = _
"=Sum(" & Cells(RowToFind, 5).Address(False, False) & ":" & _
Cells(RowToFind, ColToFind - 1).Address(False, False) & ")"

The False's after Address made the references relative. You can drop them
and the parens they are in if you want absolute references.

--
Jim
| Yes, reading back over it, it is a bit obscure. Let's try again, because
the
| second one is close to what I need.
|
| I am writing code that looks at a specific cell (RowToFind,ColToFind) and
| checks if it has an error condition, in this case I know it will be a #REF
| error. If it is errored then I want to sum all cells from (RowToFind, 5)
to
| (RowToFind, ColToFind - 1) and write that number into (RowToFind,
ColToFind).
| The following is the code I tried:
|
| If IsError(Cells(RowToFind, ColToFind)) Then Workbooks("SRA Detailed
Payroll
| - " & WBDate & ".xls").Sheets("Shreveport").Cells(RowToFind, ColToFind) =
| "=Sum(cells(RowToFind, 5):cells(rowtofind, coltofind[-1]))"
|
|
| I think I am ok up to the =Sum command.
|
| Thanks again for your help and patience.