Cannot Paste formula to cells via VB
 

I am trying to link several cells on different worksheets
to one single cell using a Subroutine. It works for a
couple of links but when there is more then about 10 links
the Subroutine won't paste it into the cell and it errors
with "Error 1004, Application defined or Object defined
error". any help?

PS this is the problematic Code.

MyString = Cells(Row, Col).Formula
MyString = Replace(MyString, SheetName1, "") 'Remove
missing link
MyString = Replace(MyString, "++", "+")
MyString = Replace(MyString, "=+", "=")
If MyString = "=" Then MyString = ""
Cells(Row, Col).Formula = MyString

Cannot Paste formula to cells via VB
 

I suspect you are producing an invalid formula with your manipulations and
that is causing an error. Doing one formula at a time would not cause an
error, even if you were looping over many cells.

Think you need to do a

debug.print myString
Cells(Row, Col).Formula = MyString

then if you get the error, look in the immediate window to see what the
formula looks like. (or go to debug and hover the mouse over the mystring
variable or do ? mystring in the immediate window.

--
Regards,
Tom Ogilvy



"Owen" wrote in message
...
I am trying to link several cells on different worksheets
to one single cell using a Subroutine. It works for a
couple of links but when there is more then about 10 links
the Subroutine won't paste it into the cell and it errors
with "Error 1004, Application defined or Object defined
error". any help?

PS this is the problematic Code.

MyString = Cells(Row, Col).Formula
MyString = Replace(MyString, SheetName1, "") 'Remove
missing link
MyString = Replace(MyString, "++", "+")
MyString = Replace(MyString, "=+", "=")
If MyString = "=" Then MyString = ""
Cells(Row, Col).Formula = MyString

Cannot Paste formula to cells via VB
 

I Have tried that and the formula is valid. I also wrote
it in manually into the cell and that worked. however it
still errors when I try to paste large Formulas.

-----Original Message-----
I suspect you are producing an invalid formula with your
manipulations and
that is causing an error. Doing one formula at a time
would not cause an
error, even if you were looping over many cells.

Think you need to do a

debug.print myString
Cells(Row, Col).Formula = MyString

then if you get the error, look in the immediate window
to see what the
formula looks like. (or go to debug and hover the mouse
over the mystring
variable or do ? mystring in the immediate window.

--
Regards,
Tom Ogilvy



"Owen" wrote in message
...
I am trying to link several cells on different
worksheets
to one single cell using a Subroutine. It works for a
couple of links but when there is more then about 10
links
the Subroutine won't paste it into the cell and it
errors
with "Error 1004, Application defined or Object defined
error". any help?

PS this is the problematic Code.

MyString = Cells(Row, Col).Formula
MyString = Replace(MyString,
SheetName1, "") 'Remove
missing link
MyString = Replace(MyString, "++", "+")
MyString = Replace(MyString, "=+", "=")
If MyString = "=" Then MyString = ""
Cells(Row, Col).Formula = MyString


.