writing a formula in VBA
 

I have 2 spreadsheets. How does one write code to have a specific cell in
one spreadsheet equal the value of another specific cell in a different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location: (something
like below)
Sheets("Sheet1").Range("A1").Formula =
"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a specific cell in
one spreadsheet equal the value of another specific cell in a different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =
"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

I'll use the exact sub procedure I have:

Sub ModifyFormulas()

ActiveSheet.Range("A1").Formula = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"

End Sub

When I run this, I get run time error 1004; application defined or object
defined error

Not sure where I am going wrong

"Bob Phillips" wrote in message
...
Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a specific cell
in
one spreadsheet equal the value of another specific cell in a different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =

"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

Rick,

Now I get it. You are using the Formula property, but providing R1C1
notation.

Try

ActiveSheet.Range("A1").FormulaR1C1 = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I'll use the exact sub procedure I have:

Sub ModifyFormulas()

ActiveSheet.Range("A1").Formula = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"

End Sub

When I run this, I get run time error 1004; application defined or object
defined error

Not sure where I am going wrong

"Bob Phillips" wrote in message
...
Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a specific cell
in
one spreadsheet equal the value of another specific cell in a
different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =

"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

Thanks Bob for your quick reply, however, I am still getting the same error
message using the formula you recommended. Any other suggestions?

-rick

"Bob Phillips" wrote in message
...
Rick,

Now I get it. You are using the Formula property, but providing R1C1
notation.

Try

ActiveSheet.Range("A1").FormulaR1C1 = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I'll use the exact sub procedure I have:

Sub ModifyFormulas()

ActiveSheet.Range("A1").Formula = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"

End Sub

When I run this, I get run time error 1004; application defined or
object
defined error

Not sure where I am going wrong

"Bob Phillips" wrote in message
...
Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a specific
cell
in
one spreadsheet equal the value of another specific cell in a
different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =


"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

Rick,

Sorry, I'm a prat. I was so busy seeing the obvious that I missed correcting
the syntax, Try this now

ActiveSheet.Range("A1").FormulaR1C1 = "='G:\Weekly
Reports\[WR_Employee.xls]Summary'!R82C4"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
Thanks Bob for your quick reply, however, I am still getting the same
error
message using the formula you recommended. Any other suggestions?

-rick

"Bob Phillips" wrote in message
...
Rick,

Now I get it. You are using the Formula property, but providing R1C1
notation.

Try

ActiveSheet.Range("A1").FormulaR1C1 = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I'll use the exact sub procedure I have:

Sub ModifyFormulas()

ActiveSheet.Range("A1").Formula = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"

End Sub

When I run this, I get run time error 1004; application defined or
object
defined error

Not sure where I am going wrong

"Bob Phillips" wrote in message
...
Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a specific
cell
in
one spreadsheet equal the value of another specific cell in a
different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =


"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

I've tried a few different versions and unless I am pointing to the same
worksheet, I can't get anything to work. Even these fail:

Sheets("Sheet2").Range("A1").Formula = "=Sheets("Sheet1").Range("A4")"
Sheets("Sheet2").Range("A1").FormulaR1C1 = "=Sheet1!A1A4"

any help would be appreciated.

Thank you in advance

-Rick

"Rick B" wrote in message
...
Thanks Bob for your quick reply, however, I am still getting the same
error
message using the formula you recommended. Any other suggestions?

-rick

"Bob Phillips" wrote in message
...
Rick,

Now I get it. You are using the Formula property, but providing R1C1
notation.

Try

ActiveSheet.Range("A1").FormulaR1C1 = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I'll use the exact sub procedure I have:

Sub ModifyFormulas()

ActiveSheet.Range("A1").Formula = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"

End Sub

When I run this, I get run time error 1004; application defined or
object
defined error

Not sure where I am going wrong

"Bob Phillips" wrote in message
...
Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a specific
cell
in
one spreadsheet equal the value of another specific cell in a
different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =


"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

Ahhhh.....much better.....never thought of trying the single quote.....works
much better......thank you very much for your help.

-Rick


"Bob Phillips" wrote in message
...
Rick,

Sorry, I'm a prat. I was so busy seeing the obvious that I missed
correcting
the syntax, Try this now

ActiveSheet.Range("A1").FormulaR1C1 = "='G:\Weekly
Reports\[WR_Employee.xls]Summary'!R82C4"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
Thanks Bob for your quick reply, however, I am still getting the same
error
message using the formula you recommended. Any other suggestions?

-rick

"Bob Phillips" wrote in message
...
Rick,

Now I get it. You are using the Formula property, but providing R1C1
notation.

Try

ActiveSheet.Range("A1").FormulaR1C1 = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I'll use the exact sub procedure I have:

Sub ModifyFormulas()

ActiveSheet.Range("A1").Formula = "=G:\Weekly
Reports\[WR_Employee.xls]Summary!R82C4"

End Sub

When I run this, I get run time error 1004; application defined or
object
defined error

Not sure where I am going wrong

"Bob Phillips" wrote in message
...
Rick,

You have confused me. What is wrong with the code you have posted?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rick B" wrote in message
...
I have 2 spreadsheets. How does one write code to have a
specific
cell
in
one spreadsheet equal the value of another specific cell in a
different
spreadsheet.

I understand the following works:
Sheets("Sheet1").Range("A1").Formula =
"=[SpreadsheetName]WorksheetName!R1A1"

but how does one point to a spreadsheet in a different location:
(something
like below)
Sheets("Sheet1").Range("A1").Formula =



"=C:\SomeDirectory\SomeOtherDirectory\[SpreadsheetName]WorksheetName!R1A1"

writing a formula in VBA
 

Rick

Us

ActiveSheet.Range("A1").Formula = "='G:\Weekl
Reports\[WR_Employee.xls]Summary'!R82C4

HT
Reij