Custom defined formula
 

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

Custom defined formula
 

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

Custom defined formula
 

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

"billq" wrote in message
...
Hi Bob,
I get the correct result in the immediate window. It is when I run the
macro in the speadsheet is where I get the value error. It must be the
way
I am sending the address in as an argument.
Thanks for the help.
bill


"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