Custom formula

[billQ]

Hello, I have a sub which copies a formula to 14 columns. Each column
formula has a calculated range above the cell where the formula is placed.
The declaration of the formula is as follows.

Public Function TotalWorkTime(unit As Long, rgnAdd As String) As Double
'here i try to set a new range to the address of rgnAdd
Set rgn2 = Range( rgn )

The routine which copies the formula into the desired cells is
dim rangeAddress as string

For x = 3 To 16
Set rgn = Range(Cells(rowFrom, x), Cells(rowTo, x))
rangeAddress = rgn.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(rowOnUnitsPage - 2, x).Formula = "=TotalWorkTime(" _
& unit & "," & rangeAddress & ")"
Next x

With the above code the formula in the formula bar is correct and the range
adjusts whenever I insert or delete a row. Unfortunately, I get a #Value
error in the cell. When debugging, I noticed the value of rgnAdd is missing
when the program enters the TotalWorkTime function. Therefore rgn2 is never
set and the sub is exited without execution.
If I send the function rgnAdd like the following
Cells(rowOnUnitsPage - 2, x).Formula = "=TotalWorkTime(" _
& unit & "," & Chr(34) & rangeAddress & Chr(34) & ")"

I get the correct value calculated but the range will not change as I insert
or delete rows.
Any thoughts would be greatly appreciated
thanks.
billQ

 

[BrianB]

I think that your problem is due to Excel not being able to convert
the code into a formula correctly in one go. Try converting your
formula to a string in an intermediate step, and use that (when you
can also check it is correct - it looks suspect to me). I am unable to
test your code because there is no indication of what variables are
produced.

Don't try to do too much in one line of code. Break the process down
into more simple, easily managed, checkable steps.

Dim MyRange as Range
Dim MyFormula As String
'-----------------------
Set MyRange = ......................
MsgBox(MyRange.Address)

MyFormula = "=TotalWorkTime(" _
& unit & "," & Chr(34) & rangeAddress & Chr(34) & ")"
MsgBox (MyString)

*To use Excel's ability to make the formula relative we have to put it
into the whole range in one go :-

MyRange.Formula = MyFormula


Regards
BrianB
========================================================

 

[billQ]

thanks for the help Brian, I change the formula to a string and it works
like a champ
billQ