Function results incorrectly applied to multiple worksheets

[darrenkinley]

Hi,

I have some worksheets with data that I've created pivot tables for and do some more calculations
on the results of the pivot tables (columns to the right of the generated pivot table). Each data set is on its
own worksheet and has it's own pivot table in a separate worksheet.

I created a function that takes as parameter a cell (column A) and then returns the previous row
(in the column) with a non-blank value. The pivot tables look like this and calling the function
in rows 4, 3, 2, 1 with parameter A4, A3, A2, A1 all return the value in A1.


A1 - B1 - C1
- B2 - C2
- B3 - C3
- C4

The problem is that the last calculated results of the function are being applied to multiple worksheets (same row/column but different worksheet).
If I go to the worksheet with PT1 results and update those columns right of the PT, save/close,
reopen Excel and go to the worksheet with PT2 results I see that the results of the function from PT1.

Can anyone tell me how to limit the scope of the results to their respective worksheet?

Here's my function.

Function PT_Site_Name(Cell As Range)
    Dim r As Range
        Set r = Cell
        Set r = ActiveSheet.Range(Cells(1, r.Column), Cells(r.Row, r.Column)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        PT_Site_Name = r.Value
End Function

Update: I created a simplified and reduced in size spreadsheet having the issue (to upload) but had the issue only once. I cannot reliably/consistently reproduce the problem and will park this until I can provide clear/simple steps to reproduce.

Thanks,
Darren

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[alansidman]

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

However, if you continue to not use code tags, you can expect to have your thread BLOCKED until you add them yourself.

[ByteMarks]

Maybe try:

Function PT_Site_Name(Cell As Range)
    Dim r As Range
    Set r = Worksheets(Cell.Parent.Name).Range(Cells(1, Cell.Column), Cells(Cell.Row, Cell.Column)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    PT_Site_Name = r.Value
End Function

[romperstomper]

BTW, you can use cell.worksheet rather than worksheets(cell.parent.name). You'll also need to qualify the Cells calls with the same sheet.

[ByteMarks]

Thanks Rory
Adjusted the code.

Function PT_Site_Name(Cell As Range)
    Dim r As Range
    With Cell.Worksheet
        Set r = .Range(.Cells(1, Cell.Column), .Cells(Cell.Row, Cell.Column)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    End With
    PT_Site_Name = r.Value
End Function