Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom formula
thanks for the help Brian, I change the formula to a string and it works
like a champ billQ "billQ" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use a formula in custom autofilter | Excel Worksheet Functions | |||
custom field with custom formula in pivot table! | Excel Worksheet Functions | |||
custom add formula | Excel Worksheet Functions | |||
Need Custom format, not a formula | Excel Discussion (Misc queries) | |||
Custom defined formula | Excel Programming |