Using VBA to create cell formulas

[Guest]

Using the following code causes an error 1004

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

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

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

 

[Bob Phillips]

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)

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