Hello,
I have the following sub which copies a formula into a cell. When I pass
the address string into the formula I have #value error in the cell
containing the formula. If I eliminate passing in the string argument then
the Calc return value of 2 is displayed. Why is the formula output affected
by the argument input which is not even used.

Thanks
BQ


Sub formulaStuff()

Dim z As Range
Dim r As Integer, c As Integer
Dim str As String

Set z = Range("A1", Range("A1").End(xlDown))
str = z.Address
r = z.Rows.Count
c = z.Columns.Count

Cells(r + 2, 1).Select
ActiveCell.Formula = "=Calc(" & str & ")"


End Sub

Function Calc(strAddress As String) As Long
Calc = 4 / 2
End Function

Bill,

I don't get the problem.

If I type
?calc(Range("A1", Range("A1").End(xlDown)).Address)
in the immediate window, I get 2.

Do you get the same?

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"billQ" wrote in message
...
Hello,
I have the following sub which copies a formula into a cell. When I pass
the address string into the formula I have #value error in the cell
containing the formula. If I eliminate passing in the string argument
then
the Calc return value of 2 is displayed. Why is the formula output
affected
by the argument input which is not even used.

Thanks
BQ


Sub formulaStuff()

Dim z As Range
Dim r As Integer, c As Integer
Dim str As String

Set z = Range("A1", Range("A1").End(xlDown))
str = z.Address
r = z.Rows.Count
c = z.Columns.Count

Cells(r + 2, 1).Select
ActiveCell.Formula = "=Calc(" & str & ")"


End Sub

Function Calc(strAddress As String) As Long
Calc = 4 / 2
End Function