ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Replacing "Div/0!" errors (https://www.excelbanter.com/excel-programming/386178-replacing-div-0-errors.html)

Lorenda
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

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

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

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


All times are GMT +1. The time now is 08:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com