Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Format for User Defined Function | Excel Discussion (Misc queries) | |||
User Defined/Custom Input | Excel Discussion (Misc queries) | |||
User-defined (Custom) Charts | Charts and Charting in Excel | |||
Custom lists - not following defined order | Excel Worksheet Functions | |||
Custom lists - not following defined order | Excel Worksheet Functions |