Error 1004 on storing formula in a cell

[Ham]

I'm trying to store a large formula into a cell using 'Activesheet.Cells(row, col) = ....' I get the 'Run-time error 1004 Application-defined or object-dfined error.' The formula is correct. In desperation I even tried copying an existing working formula from a cell and storing it back to the same cell. Same error. I have done this with other spreadsheets with no problem. Any suggestions as to what might be going on here?

Thanks. -- Ham

 

[rmikesmith]

Ham,

I was able to use your construct

Activesheet.Cells(row,col) = "..."

and get it to work properly. If, however, the formula is incorrect (for example "=A4+C$&quot, I get Error 1004. Perhaps you could post your formula.

 

[Ham]

I'll post the formula. It's a stinker, but I'm pretty sure it's OK. I changed the macro to store it with a leading apostophe so it would go into the cell as a comment. Then I removed the apostrophe and it worked. Anyway, here it is:
=IF(D53=$C$5;0;IF(($C40*E40+$C41*E41+$C42*E42+$C43*E43+$C44*E44+$C45*E45+$C46*E46+$C47*E47)<$C$6;$C$6;IF(($C40*E40+$C41*E41+$C42*E42+$C43*E43+$C44*E44+$C45*E45+$C46*E46+$C47*E47+D53)>$C$5;$C$5-D53;$C40*E40+$C41*E41+$C42*E42+$C43*E43+$C44*E44+$C45*E45+$C46*E46+$C47*E47)))

 

[rmikesmith]

Ham,

Your formula contains semi-colons separating the IF function arguments. These should be commas. Your function string works for me with this change (i.e., I can assign it to a cell using VBA code). Try it.

Regards,
M. Smith

 

[Ham]

I appreciate your trying this. The comma change doesn't work for me. I must have the separator character defined differently someplace. What I have done to verify the formula is write to a text file exactly what I wanted to store in the cell. Then, I cut/paste from the file to the cell. The posted formula goes in OK. If I do introduce an error, I get an error message when I do the paste. It must have something to do with the act of storing, which is a very simple statement: 'ActiveSheet.Cells(RevRow, iCol) = CellCont'. RevRow and iCol are correct. CellCont contains the formula. I am at a total loss. -- Ham

 

[Ham]

This is really weird. When I enter a formula directly into a cell, I have to use semi-colons or I get an error. However, when I then read the formula in VBA code, all the semi-colons were commas. I did then use commas in my VBA code which generates the forumlas and they enter ok, but they display in the spreadsheet as semi-colons. But it works. Go figure.

Anyway, you were right - it is a comma vs. semi-colon issue. Thank you very, very much! -- Ham