writing a formula to a cell

[Guest]

Does anyone know why I get an error when I try to write this formula to a cell?

Range("A2").formula = "='LP-A'!E10"

where LP-A is the name of a sheet and E10 is the cell whose value that I
want to put in A2

I get a trappable error 1004 description is "Application - defined or
object-defined error"

Any help would be greatly appreciated!

 

[Vasant Nanavati]

I can't replicate your error in Excel 2002.

 

[JE McGimpsey]

I can't replicate your results - do you have the worksheet protected?

 

[Dave Peterson]

You sure that the name of that worksheet is LP-A?

Maybe extra (leading/trailing/embedded spaces)???

Maybe that worksheet doesn't exist in the activeworkbook.

 

[Guest]

LP-A is the name of the sheet, and the worksheet is not protected. The
strange thing is that I can record a macro that executes when I push a
button. This macro correctly inserts the formula in the cell, but when my
code wants to put the formula in the cell using the same code as the formula
I get the error.

I can't figure it out.

 

[Dave Peterson]

Are you using xl97?

Are you running this code from a commandbutton from the control toolbox toolbar
placed on a worksheet? (Or any control from that control toolbox toolbar?)

If yes, try changing the .takefocusonclick property for that button to False.

If the control doesn't have that property, try adding:
activecell.activate

to the top of the code.

(This was a bug that was fixed in xl2k.)