Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1 problem
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1 problem
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? wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1 problem
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1 problem
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. wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1 problem
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. " wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormulaR1C1 problem
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. On Apr 20, 6:56 pm, JLGWhiz wrote: 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. " wrote: 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What Does FormulaR1C1 Do? | Excel Programming | |||
Formular1c1 | Excel Programming | |||
FormulaR1C1 | Excel Programming | |||
FormulaR1C1 | Excel Programming | |||
FormulaR1C1 | Excel Programming |