Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Updating with formulas
Hi,
I have created a sheet which calls the row() function and sends it to a USER defined function. This function (WeekTotal) in turn processes the data in the row. This all works as planned. However, I also have a 2nd copy of the main sheet in the same workbook, but with different values in the row. What is happening is when I calculate the fields in Sheet 1, it also sends these values to Sheet 2 (so sheet 2 is wrong). If I am in sheet 2 and calculate the fields, then the calculations are correct, but it also sends these same values to sheet 1. So now sheet 1 is wrong. These need to be independant of one another. I can't figure out why it is sending values from one sheet to another, when Function in Excel Cell =WeekTotal(ROW()) --------------------------------------------------------- Function WeekTotal(rowNum As Integer) As Single 'Columns D-J contain the shift information Dim total As Single Dim iCount As Integer Dim rangeLetter As String Dim rangeCell As String rangeLetter = "D" total = 0 On Error Resume Next 'Ignore blank days or days not following the time format For iCount = 1 To 7 rangeCell = rangeLetter & rowNum total = total + shiftLength(ActiveSheet.Range(rangeCell).Value) rangeLetter = Chr$(Asc(rangeLetter) + 1) Next On Error GoTo 0 WeekTotal = total End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Updating with formulas
Hi,
The problem seems to be that the function operates on values from the active sheet, which is not necessarily the same sheet where the function is entered. Often cell references are passed to a function as parameters, and then there is no problem. How to best solve your problem I don't know, but one way may be to use Application.Caller.Parent instead of ActiveSheet. HTH Anders Silvén "boots" skrev i meddelandet news:aDPub.435088$6C4.357510@pd7tw1no... Hi, I have created a sheet which calls the row() function and sends it to a USER defined function. This function (WeekTotal) in turn processes the data in the row. This all works as planned. However, I also have a 2nd copy of the main sheet in the same workbook, but with different values in the row. What is happening is when I calculate the fields in Sheet 1, it also sends these values to Sheet 2 (so sheet 2 is wrong). If I am in sheet 2 and calculate the fields, then the calculations are correct, but it also sends these same values to sheet 1. So now sheet 1 is wrong. These need to be independant of one another. I can't figure out why it is sending values from one sheet to another, when Function in Excel Cell =WeekTotal(ROW()) --------------------------------------------------------- Function WeekTotal(rowNum As Integer) As Single 'Columns D-J contain the shift information Dim total As Single Dim iCount As Integer Dim rangeLetter As String Dim rangeCell As String rangeLetter = "D" total = 0 On Error Resume Next 'Ignore blank days or days not following the time format For iCount = 1 To 7 rangeCell = rangeLetter & rowNum total = total + shiftLength(ActiveSheet.Range(rangeCell).Value) rangeLetter = Chr$(Asc(rangeLetter) + 1) Next On Error GoTo 0 WeekTotal = total End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Updating with formulas
Thanks, that did the trick.
"Anders S" wrote in message ... Hi, The problem seems to be that the function operates on values from the active sheet, which is not necessarily the same sheet where the function is entered. Often cell references are passed to a function as parameters, and then there is no problem. How to best solve your problem I don't know, but one way may be to use Application.Caller.Parent instead of ActiveSheet. HTH Anders Silvén "boots" skrev i meddelandet news:aDPub.435088$6C4.357510@pd7tw1no... Hi, I have created a sheet which calls the row() function and sends it to a USER defined function. This function (WeekTotal) in turn processes the data in the row. This all works as planned. However, I also have a 2nd copy of the main sheet in the same workbook, but with different values in the row. What is happening is when I calculate the fields in Sheet 1, it also sends these values to Sheet 2 (so sheet 2 is wrong). If I am in sheet 2 and calculate the fields, then the calculations are correct, but it also sends these same values to sheet 1. So now sheet 1 is wrong. These need to be independant of one another. I can't figure out why it is sending values from one sheet to another, when Function in Excel Cell =WeekTotal(ROW()) --------------------------------------------------------- Function WeekTotal(rowNum As Integer) As Single 'Columns D-J contain the shift information Dim total As Single Dim iCount As Integer Dim rangeLetter As String Dim rangeCell As String rangeLetter = "D" total = 0 On Error Resume Next 'Ignore blank days or days not following the time format For iCount = 1 To 7 rangeCell = rangeLetter & rowNum total = total + shiftLength(ActiveSheet.Range(rangeCell).Value) rangeLetter = Chr$(Asc(rangeLetter) + 1) Next On Error GoTo 0 WeekTotal = total End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas not updating | Excel Discussion (Misc queries) | |||
Formulas updating | Excel Worksheet Functions | |||
Formulas aren't updating | Excel Worksheet Functions | |||
updating formulas with named cell references in a different worksh | Excel Worksheet Functions | |||
Updating formulas | Excel Worksheet Functions |