writing a formula in VBA

[Rick B]

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"

 

[Bob Phillips]

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]

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

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)

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"

 

[Bob Phillips]

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)

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

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)

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"

 

[Rick B]

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

-rick

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)

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

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)

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"

 

[Bob Phillips]

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)

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

-rick

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)

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

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)

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"

 

[Rick B]

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

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

-rick

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)

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

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)

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"

 

[Rick B]

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

-Rick

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)

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

-rick

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)

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

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)

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"

 

[Guest]

Rick

Us

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

HT
Reij