| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing "Div/0!" errors
Is there a way I can replace any Div/0! errors without having to
copy/pastevalues first? The person I am building this for wants to be able to click into the cell and see the formula. I tried adding an "if(iserror())" around my formulas but I got an error message. Any ideas. I've copied my full macro below. Sub Format_LAC() ' Macro recorded 1/29/2007 by Lorenda Christensen Dim length As Long, data As Range, rangeofdata Range("a1").Select Set data = ActiveCell.CurrentRegion length = data.Rows.count Columns("P:P").Select Selection.Insert Shift:=xlToRight Columns("T:V").Select Selection.Insert Shift:=xlToRight Range("P1").FormulaR1C1 = "% Bill" Range("T1").FormulaR1C1 = "Amount Paid by Customer" Range("U1").FormulaR1C1 = "Commission Taken by Customer" Range("V1").FormulaR1C1 = "% of Commission Taken by Customer" Range("W1").FormulaR1C1 = "Ebill Balance" Range("X1").FormulaR1C1 = "Amount of Commission Due" Range("Y1").FormulaR1C1 = "Amount to Credit/Debit" Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" rangeofdata = "P2" & ":" & "P" & length Range("P2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("T2").FormulaR1C1 = "=RC[-5]-RC[3]" rangeofdata = "T2" & ":" & "T" & length Range("T2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("U2").FormulaR1C1 = "=RC[-7]-RC[-1]" rangeofdata = "U2" & ":" & "U" & length Range("U2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("V2").FormulaR1C1 = "=((RC[-1]*100)/RC[-8])/100)" rangeofdata = "V2" & ":" & "V" & length Range("V2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("X2").FormulaR1C1 = "=RC[-5]*RC[-10]" rangeofdata = "X2" & ":" & "X" & length Range("X2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("Y2").FormulaR1C1 = "=RC[-4]-RC[-1]" rangeofdata = "Y2" & ":" & "Y" & length Range("Y2").Select Selection.AutoFill Destination:=Range(rangeofdata) Columns("H:I").Select Selection.NumberFormat = "mmm-d-yyyy h:mm AM/PM" Range("N:O,T:U,W:Y").Select Selection.NumberFormat = "#,##0.00_);(#,##0.00)" Range("V:V,S:S,P:P").Select Selection.NumberFormat = "0%" Range("A1").Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Replacing "Div/0!" errors
Thanks, Tom. Good catch!
Regards, Vergel Adriano "Tom Ogilvy" wrote: Think you have a typo in your suggestion: Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2])" should be: Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, """", (RC[-2]-RC[-1]*100%)/RC[-2])" verfied with the immediate window" ? "=IF(RC[-2]=0, """", (RC[-2]-RC[-1]*100%)/RC[-2])" =IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2]) -- Regards, Tom Ogilvy "Vergel Adriano" wrote: Try checking the divisor if its 0 first. For example, instead of this Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" try this Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, 0, (RC[-2]-RC[-1]*100%)/RC[-2])" The above will show 0 if the operation will result in Div/0!. If you want it to show blank, do it this way Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2])" -- Hope that helps. Vergel Adriano "Lorenda" wrote: Is there a way I can replace any Div/0! errors without having to copy/pastevalues first? The person I am building this for wants to be able to click into the cell and see the formula. I tried adding an "if(iserror())" around my formulas but I got an error message. Any ideas. I've copied my full macro below. Sub Format_LAC() ' Macro recorded 1/29/2007 by Lorenda Christensen Dim length As Long, data As Range, rangeofdata Range("a1").Select Set data = ActiveCell.CurrentRegion length = data.Rows.count Columns("P:P").Select Selection.Insert Shift:=xlToRight Columns("T:V").Select Selection.Insert Shift:=xlToRight Range("P1").FormulaR1C1 = "% Bill" Range("T1").FormulaR1C1 = "Amount Paid by Customer" Range("U1").FormulaR1C1 = "Commission Taken by Customer" Range("V1").FormulaR1C1 = "% of Commission Taken by Customer" Range("W1").FormulaR1C1 = "Ebill Balance" Range("X1").FormulaR1C1 = "Amount of Commission Due" Range("Y1").FormulaR1C1 = "Amount to Credit/Debit" Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" rangeofdata = "P2" & ":" & "P" & length Range("P2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("T2").FormulaR1C1 = "=RC[-5]-RC[3]" rangeofdata = "T2" & ":" & "T" & length Range("T2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("U2").FormulaR1C1 = "=RC[-7]-RC[-1]" rangeofdata = "U2" & ":" & "U" & length Range("U2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("V2").FormulaR1C1 = "=((RC[-1]*100)/RC[-8])/100)" rangeofdata = "V2" & ":" & "V" & length Range("V2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("X2").FormulaR1C1 = "=RC[-5]*RC[-10]" rangeofdata = "X2" & ":" & "X" & length Range("X2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("Y2").FormulaR1C1 = "=RC[-4]-RC[-1]" rangeofdata = "Y2" & ":" & "Y" & length Range("Y2").Select Selection.AutoFill Destination:=Range(rangeofdata) Columns("H:I").Select Selection.NumberFormat = "mmm-d-yyyy h:mm AM/PM" Range("N:O,T:U,W:Y").Select Selection.NumberFormat = "#,##0.00_);(#,##0.00)" Range("V:V,S:S,P:P").Select Selection.NumberFormat = "0%" Range("A1").Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Replacing "Div/0!" errors
Try checking the divisor if its 0 first. For example, instead of this
Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" try this Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, 0, (RC[-2]-RC[-1]*100%)/RC[-2])" The above will show 0 if the operation will result in Div/0!. If you want it to show blank, do it this way Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2])" -- Hope that helps. Vergel Adriano "Lorenda" wrote: Is there a way I can replace any Div/0! errors without having to copy/pastevalues first? The person I am building this for wants to be able to click into the cell and see the formula. I tried adding an "if(iserror())" around my formulas but I got an error message. Any ideas. I've copied my full macro below. Sub Format_LAC() ' Macro recorded 1/29/2007 by Lorenda Christensen Dim length As Long, data As Range, rangeofdata Range("a1").Select Set data = ActiveCell.CurrentRegion length = data.Rows.count Columns("P:P").Select Selection.Insert Shift:=xlToRight Columns("T:V").Select Selection.Insert Shift:=xlToRight Range("P1").FormulaR1C1 = "% Bill" Range("T1").FormulaR1C1 = "Amount Paid by Customer" Range("U1").FormulaR1C1 = "Commission Taken by Customer" Range("V1").FormulaR1C1 = "% of Commission Taken by Customer" Range("W1").FormulaR1C1 = "Ebill Balance" Range("X1").FormulaR1C1 = "Amount of Commission Due" Range("Y1").FormulaR1C1 = "Amount to Credit/Debit" Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" rangeofdata = "P2" & ":" & "P" & length Range("P2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("T2").FormulaR1C1 = "=RC[-5]-RC[3]" rangeofdata = "T2" & ":" & "T" & length Range("T2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("U2").FormulaR1C1 = "=RC[-7]-RC[-1]" rangeofdata = "U2" & ":" & "U" & length Range("U2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("V2").FormulaR1C1 = "=((RC[-1]*100)/RC[-8])/100)" rangeofdata = "V2" & ":" & "V" & length Range("V2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("X2").FormulaR1C1 = "=RC[-5]*RC[-10]" rangeofdata = "X2" & ":" & "X" & length Range("X2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("Y2").FormulaR1C1 = "=RC[-4]-RC[-1]" rangeofdata = "Y2" & ":" & "Y" & length Range("Y2").Select Selection.AutoFill Destination:=Range(rangeofdata) Columns("H:I").Select Selection.NumberFormat = "mmm-d-yyyy h:mm AM/PM" Range("N:O,T:U,W:Y").Select Selection.NumberFormat = "#,##0.00_);(#,##0.00)" Range("V:V,S:S,P:P").Select Selection.NumberFormat = "0%" Range("A1").Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Replacing "Div/0!" errors
Think you have a typo in your suggestion:
Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2])" should be: Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, """", (RC[-2]-RC[-1]*100%)/RC[-2])" verfied with the immediate window" ? "=IF(RC[-2]=0, """", (RC[-2]-RC[-1]*100%)/RC[-2])" =IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2]) -- Regards, Tom Ogilvy "Vergel Adriano" wrote: Try checking the divisor if its 0 first. For example, instead of this Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" try this Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, 0, (RC[-2]-RC[-1]*100%)/RC[-2])" The above will show 0 if the operation will result in Div/0!. If you want it to show blank, do it this way Range("P2").FormulaR1C1 = "=IF(RC[-2]=0, "", (RC[-2]-RC[-1]*100%)/RC[-2])" -- Hope that helps. Vergel Adriano "Lorenda" wrote: Is there a way I can replace any Div/0! errors without having to copy/pastevalues first? The person I am building this for wants to be able to click into the cell and see the formula. I tried adding an "if(iserror())" around my formulas but I got an error message. Any ideas. I've copied my full macro below. Sub Format_LAC() ' Macro recorded 1/29/2007 by Lorenda Christensen Dim length As Long, data As Range, rangeofdata Range("a1").Select Set data = ActiveCell.CurrentRegion length = data.Rows.count Columns("P:P").Select Selection.Insert Shift:=xlToRight Columns("T:V").Select Selection.Insert Shift:=xlToRight Range("P1").FormulaR1C1 = "% Bill" Range("T1").FormulaR1C1 = "Amount Paid by Customer" Range("U1").FormulaR1C1 = "Commission Taken by Customer" Range("V1").FormulaR1C1 = "% of Commission Taken by Customer" Range("W1").FormulaR1C1 = "Ebill Balance" Range("X1").FormulaR1C1 = "Amount of Commission Due" Range("Y1").FormulaR1C1 = "Amount to Credit/Debit" Range("P2").FormulaR1C1 = "=((RC[-2]-RC[-1]*100%)/RC[-2])" rangeofdata = "P2" & ":" & "P" & length Range("P2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("T2").FormulaR1C1 = "=RC[-5]-RC[3]" rangeofdata = "T2" & ":" & "T" & length Range("T2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("U2").FormulaR1C1 = "=RC[-7]-RC[-1]" rangeofdata = "U2" & ":" & "U" & length Range("U2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("V2").FormulaR1C1 = "=((RC[-1]*100)/RC[-8])/100)" rangeofdata = "V2" & ":" & "V" & length Range("V2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("X2").FormulaR1C1 = "=RC[-5]*RC[-10]" rangeofdata = "X2" & ":" & "X" & length Range("X2").Select Selection.AutoFill Destination:=Range(rangeofdata) Range("Y2").FormulaR1C1 = "=RC[-4]-RC[-1]" rangeofdata = "Y2" & ":" & "Y" & length Range("Y2").Select Selection.AutoFill Destination:=Range(rangeofdata) Columns("H:I").Select Selection.NumberFormat = "mmm-d-yyyy h:mm AM/PM" Range("N:O,T:U,W:Y").Select Selection.NumberFormat = "#,##0.00_);(#,##0.00)" Range("V:V,S:S,P:P").Select Selection.NumberFormat = "0%" Range("A1").Select End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "num#" errors .. how to average a group with a "num#" error | Excel Discussion (Misc queries) | |||
| Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
| Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| How do I stop Excel from replacing "(c)" with copyright symbol? | Excel Discussion (Misc queries) | |||
Linear Mode
