Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a formula in a cell? Application-defined error.
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a formula in a cell? Application-defined error.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a formula in a cell? Application-defined error.
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a formula in a cell? Application-defined error.
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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a formula in a cell? Application-defined error.
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set a formula in a cell? Application-defined 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 & """" & _ " & Indirect(" & Str2 & Str3 & ")" RA.FormulaR1C1 = Str4 End Sub -- Regards, Tom Ogilvy "John Wirt" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Macro error : Application-defined or object-defined error | Excel Discussion (Misc queries) | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
Runtime Error 1004 -- Application Defined or Object Defined Error | Excel Programming |