How to set a formula in a cell? Application-defined error.

Here is the beginning of a procedure I wrote to enter a formula in a cell
that links the cell to a value in some other cell. When I run this
procedure, I get a 1004 error: "Application-defined or obejct-defined error"
at the last statement.

Public Sub InsertLinkedTableTitle()
'This macro links the suppl table title in draft
'indicators to the CODE box of form
Dim RA As Range
Set RA = ActiveSheet.Range("A1")
RA.FormulaR1C1 = "='Table S' &'Title Page'!D2"
End Sub

The problem is the 'Table S' part of the formula. If I take it (and the '&")
out of the formula, the procedure runs fine. There is something I don't know
about formatting such a text string in a formula.

JOhn Wirt

What is the purpose of 'Table s' ?
The & is used to join strings together. Example:
"a" & "b"
would return ab.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Try this:
RA.FormulaR1C1 = "=" &"Table S" &"Title Page'!D2"

"John Wirt" wrote:

> Here is the beginning of a procedure I wrote to enter a formula in a cell
> that links the cell to a value in some other cell. When I run this
> procedure, I get a 1004 error: "Application-defined or obejct-defined error"
> at the last statement.
>
> Public Sub InsertLinkedTableTitle()
> 'This macro links the suppl table title in draft
> 'indicators to the CODE box of form
> Dim RA As Range
> Set RA = ActiveSheet.Range("A1")
> RA.FormulaR1C1 = "='Table S' &'Title Page'!D2"
> End Sub
>
> The problem is the 'Table S' part of the formula. If I take it (and the '&")
> out of the formula, the procedure runs fine. There is something I don't know
> about formatting such a text string in a formula.
>
> JOhn Wirt
>
>
>

Assuming Table S is another workbook.

Public Sub InsertLinkedTableTitle()
'This macro links the suppl table title in draft
'indicators to the CODE box of form
Dim RA As Range
Set RA = ActiveSheet.Range("A1")
RA.Formula = "='[Table S.xls]Title Page'!D2"
End Sub

Don't use FormulaR1C1 if you are going to use A1 style addressing.

--
Regards,
Tom Ogilvy


"John Wirt" <someone@d.com> wrote in message
news:OU$x92VFFHA.3384@tk2msftngp13.phx.gbl...
> Here is the beginning of a procedure I wrote to enter a formula in a cell
> that links the cell to a value in some other cell. When I run this
> procedure, I get a 1004 error: "Application-defined or obejct-defined
error"
> at the last statement.
>
> Public Sub InsertLinkedTableTitle()
> 'This macro links the suppl table title in draft
> 'indicators to the CODE box of form
> Dim RA As Range
> Set RA = ActiveSheet.Range("A1")
> RA.FormulaR1C1 = "='Table S' &'Title Page'!D2"
> End Sub
>
> The problem is the 'Table S' part of the formula. If I take it (and the
'&")
> out of the formula, the procedure runs fine. There is something I don't
know
> about formatting such a text string in a formula.
>
> JOhn Wirt
>
>

It isn't formatting, but specifying the workbook name correctly.

The easiest way to resolve this sort of addressing/syntax error is to enter
the formula manually by opening that other workbook and pointing to the
correct cell. It should look like pretty much like this:

='[Table S.XLS]Title Page'!$D$2

Note that the workbook name includes the .XLS extension and is enclosed in
square brackets to separate it from the sheet name. If you were to close that
workbook, you would see that Excel would add the path to the filename. Try it
to see what I mean.

The book and sheet must be enclosed in single quotes because of the embedded
spaces in the names.

On Thu, 17 Feb 2005 21:01:56 -0500, "John Wirt" <someone@d.com> wrote:

>Here is the beginning of a procedure I wrote to enter a formula in a cell
>that links the cell to a value in some other cell. When I run this
>procedure, I get a 1004 error: "Application-defined or obejct-defined error"
>at the last statement.
>
>Public Sub InsertLinkedTableTitle()
> 'This macro links the suppl table title in draft
> 'indicators to the CODE box of form
> Dim RA As Range
> Set RA = ActiveSheet.Range("A1")
> RA.FormulaR1C1 = "='Table S' &'Title Page'!D2"
>End Sub
>
>The problem is the 'Table S' part of the formula. If I take it (and the '&")
>out of the formula, the procedure runs fine. There is something I don't know
>about formatting such a text string in a formula.
>
>JOhn Wirt
>

This comes closer to the formula to be inserted but still produces the same
error:

Public Sub InsertLinkedTableTitle()
'This macro links the suppl table title in draft
'indicators to the CODE box of form
Dim RA As Range
Dim Str1 As String, Str2 As String, Str3 As String, Str4 As String

Set RA = ActiveSheet.Range("A1")
Str1 = "Standard errors for table S"
Str2 = "'Title Page'"
Str3 = "!D2"
Str4 = "=" & "'" & Str1 & "'" & " & " & Str2 & Str3
RA.FormulaR1C1 = Str4
End Sub

The actual formula to be inserted is:

="Standard errors for table S" & 'Title page'!D2

The value of this formula is: "Standard errors for tableSER2."

Cell 'Title page'!D2 contains the string value "ER2." It's in the same
workbook as the formula.

John Wirt

Public Sub InsertLinkedTableTitle()
'This macro links the suppl table title in draft
'indicators to the CODE box of form
Dim RA As Range
Dim Str1 As String, Str2 As String, Str3 As String, Str4 As String

Set RA = ActiveSheet.Range("A1")
Str1 = "Standard errors for table S"
Str2 = "'Title Page'"
Str3 = "!D2"
str4 = "=" & """" & Str1 & """" & _
" & Indirect(" & Str2 & Str3 & ")" RA.FormulaR1C1 = Str4
End Sub

--
Regards,
Tom Ogilvy

"John Wirt" <someone@d.com> wrote in message
news:%23$6n04bFFHA.1932@TK2MSFTNGP14.phx.gbl...
> This comes closer to the formula to be inserted but still produces the
same
> error:
>
> Public Sub InsertLinkedTableTitle()
> 'This macro links the suppl table title in draft
> 'indicators to the CODE box of form
> Dim RA As Range
> Dim Str1 As String, Str2 As String, Str3 As String, Str4 As String
>
> Set RA = ActiveSheet.Range("A1")
> Str1 = "Standard errors for table S"
> Str2 = "'Title Page'"
> Str3 = "!D2"
> Str4 = "=" & "'" & Str1 & "'" & " & " & Str2 & Str3
> RA.FormulaR1C1 = Str4
> End Sub
>
> The actual formula to be inserted is:
>
> ="Standard errors for table S" & 'Title page'!D2
>
> The value of this formula is: "Standard errors for tableSER2."
>
> Cell 'Title page'!D2 contains the string value "ER2." It's in the same
> workbook as the formula.
>
> John Wirt
>
>
>