How to avoid #DIV/0! when pasting formula =RC[-1]/RC[-2] in a RANGE

[johnod33]

I am copying and pasting a formula into column I rows 4 thru predetermined count of DBF records.

The constants are :
#DEFINE xlSum -4157
#DEFINE xlFillDefault 0
#DEFINE xlAutoFill 4
#DEFINE xlPasteFormulas -4123
#DEFINE xlPasteValues -4163
#DEFINE xlNone -4142

.Range("I4").Select
.ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
.Range("I4").copy
.Range("I4:I"+ALLTRIM(STR(vfincount+4))).Pastespecial

This code works wonderful but I get the dreaded #DIV/0! where RC[-2] = 0

I am thinking the formula has to be defined like this:
IF(RC[-2]=0,"","=RC[-1]/RC[-2]")
but of course it doesn't work.

Does anyone know how to avoid the #DIV/0! ?

John O'D

 

[JRB-Bldr]

As you already have stated the formula
IF(RC[-2]=0,"","=RC[-1]/RC[-2]")
would not work since the results of the divide operation would result in a numeric value and your IF's alternative value would be a character.

Have you tried creating the formula by hand in Excel while recording the action as a Macro?

That is the best way to determine what Automation code to use.

Once you examine the VBA code from the Macro, you should be able to determine the VFP code.

Good Luck,
JRB-Bldr

 

[johnod33]

Thanks JRB-Bldr,

I just changed the "" to 0 and it worked fine.
Before:
IF(RC[-2]=0,"","=RC[-1]/RC[-2]")

After:
IF(RC[-2]=0,0,"=RC[-1]/RC[-2]")

Thanks again!

John O'D