FormulaR1C1 problem

[satlow]

I have a macro that contains the line:

..Cells(irow + 2, 5).FormulaR1C1 = "=R[-1]C*R[-2]C3"

The first time a worksheet is created and the above code is executed
when irow=15, the formula produced in E17 is "=A65536*$C65535". If
I run it again, the code executes properly and cell E17 gets the
formula "E16*$C$15". Does anyone have any ideas? I've exhausted all
mine, and have concluded it's an Excel bug.

 

[Dave Peterson]

You have a . in front of that .cells() portion.

What's the "With" statement look like?

And are you sure you know what's in iRow?

I have a macro that contains the line:

.Cells(irow + 2, 5).FormulaR1C1 = "=R[-1]C*R[-2]C3"

The first time a worksheet is created and the above code is executed
when irow=15, the formula produced in E17 is "=A65536*$C65535". If
I run it again, the code executes properly and cell E17 gets the
formula "E16*$C$15". Does anyone have any ideas? I've exhausted all
mine, and have concluded it's an Excel bug.

 

[satlow]

The "with" refers to the worksheet. The macro creates the worksheet
if it does not already exist. I added a watch on cell E17, and could
see that the incorrect formula is produced when the statement is
executed. When the macro completes, I can check the cell, and see
that the incorrect formula is still there. The second time I run the
macro, the watch shows the correct formula for the cell. The row
numbers produced the first time lead me to believe this could be an
overflow condition. But why?

 

[Dave Peterson]

I've never seen an overflow error that didn't cause the code to stop.

Maybe iRow isn't what you believe to be the first time.

 

[Guest]

Going with what Dave said, if your cursor resides on the last row or below
when you start the macro, and you have something like iRow =
ActiveCell.End(xlDown).Row, you are sending the execution to the bottom with
Cells(irow + 2, 5).FormulaR1C1 = "=R[-1]C*R[-2]C3"
You would have to make sure your cursor has some rows with data in them
beneath it to avoid the problem and can do that by adding

Range("A1").Activate

as the first command in the macro.

 

[satlow]

Thanks, all, but I don't think that's it. Here's more of the macro
code:

With ThisWorkbook.Worksheets(outname)
irow = 15
For iwp = 0 To 200
' .Cells(irow + 2, 5).FormulaR1C1 = "=R[-1]C*R[-2]C3"
.Cells(irow + 2, 5).Formula = "=e" & irow + 1 & "*$c" &
irow
irow=irow+6
Next iwp
End With

The commented line gives the incorrect formula. The line below it
gives the correct results. As far as I can see, my cursor is never
involved.

Going with what Dave said, if your cursor resides on the last row or below
when you start the macro, and you have something like iRow =
ActiveCell.End(xlDown).Row, you are sending the execution to the bottom with
Cells(irow + 2, 5).FormulaR1C1= "=R[-1]C*R[-2]C3"
You would have to make sure your cursor has some rows with data in them
beneath it to avoid the problem and can do that by adding

Range("A1").Activate

as the first command in the macro.

I have a macro that contains the line:

..Cells(irow + 2, 5).FormulaR1C1= "=R[-1]C*R[-2]C3"

The first time a worksheet is created and the above code is executed
when irow=15, the formula produced in E17 is "=A65536*$C65535". If
I run it again, the code executes properly and cell E17 gets the
formula "E16*$C$15". Does anyone have any ideas? I've exhausted all
mine, and have concluded it's an Excel bug.- Hide quoted text -

- Show quoted text -