Custom defined formula

[billQ]

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

 

[Bob Phillips]

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?

 

[Charles Williams]

Hi Bill,

Change
Function Calc(strAddress As String) As Long
to
Function Calc(strAddress As range) As Long

or change

ActiveCell.Formula = "=Calc('" & str & "')"
to
ActiveCell.Formula = "=Calc(" & Chr(34) & str & Chr(34) & ")"

probably the first suggestion is what you want.


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com