Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for dealing with #REF
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for dealing with #REF
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 "Kevin Porter" wrote in message ... |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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for dealing with #REF
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 "Kevin Porter" wrote in message ... |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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for dealing with #REF
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 "Kevin Porter" wrote in message ... | 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 | "Kevin Porter" wrote in message | ... | |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 | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for dealing with #REF
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for dealing with #REF
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 "Kevin Porter" wrote in message ... | 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dealing with #VALUE! | Excel Discussion (Misc queries) | |||
dealing with time | Excel Worksheet Functions | |||
Dealing with +/- Values, Need Help | Excel Worksheet Functions | |||
card dealing | Excel Programming | |||
Dealing with pop-ups | Excel Programming |