Public Function ReturnRef(s As Worksheet, r As Range) As String
Dim sheet As String
Dim address As String
sheet = s.Name & "!"
address = r.address(ReferenceStyle:=xlR1C1)
ReturnRef = sheet & address
End Function ' Return for Example "SheetName!R1C1"

--

HTH

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

"Shayne Pierce" wrote in message
...
Using the following code causes an error 1004.

Public Function ReturnRef(s As Worksheet, r As Range) As String
Dim sheet As String
Dim address As String
sheet = s.Name & "!"
address = r.address(ReferenceStyle:=xlR1C1)
ReturnReference = sheet & address
End Function ' Return for Example "SheetName!R1C1"

Public Sub FromTakeoffToEstimate(Takeoff As Worksheet, Estimate As
Worksheet) ' Drawing to Esitmate
' Setting up Equations for Estimate
Estimate.Cells(9, 2).FormulaR1C1 = "= " & ReturnRef(Takeoff,
Takeoff.Cells(2, 4)) '
....
End Sub

I was wondering if anyone can see a problem with this code or has another
way to solve this problem