Cell Updating with formulas

  • Thread starter Thread starter boots
  • Start date Start date
B

boots

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
 
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
 
Thanks, that did the trick.

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
 
Back
Top