Replacing "Div/0!" errors

L

Lorenda

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
 
V

Vergel Adriano

Thanks, Tom. Good catch!

Regards,
Vergel Adriano


Tom Ogilvy said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
V

Vergel Adriano

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])"
 
T

Tom Ogilvy

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Pivot Table with Macro using Multiple Sheets 0
R1:C1 format 2
Running Excel Code from Access 9
Replace a fixed number with a variable via popup 3
Running an Excel Macro from Access 6
Counting rows in Excel spreadsheet. 4
Macro not returning correct data 3
Adding a macro to copy worksheets 0

Top