I have a Excel with multiple sheets. In each sheet, I want a specific cell (let's call it BANK cell) to show the value of the cell above it. But there could be multiple empty cell above it until it reaches a cell that actually contains a numerical value.
Each sheet will have its own BANK cell, and each BANK cell of each sheet should have its own value (at its own sheet).
I write a VBA to achieve this. At first it looks successful. But then soon I realize, while I originally intended to let each sheet's BANK cell independent from each sheet, my VBA script actually make them all link together.
Example, in sheet 1, if the cell above BANK cell has value 100, then the BANK cell automatically display 100, perfect.
Then I go to sheet 2, do my work and eventually in sheet 2, the cell above BANK cell has value 200, and the BANK cell in sheet 2 also display 200, still perfect.
But if I now go back to view sheet 1, I will find out that the BANK cell in sheet 1 is already become 200 instead of the intended 100.
If I now reenter the formula in sheet 1 again, yes it will correct the BANK cell in sheet 1, but now sheet 2 is incorrect.
How can I "unlink" them, so that the script only find value specific to a particular sheet?
Function FindBalance() As Double
Application.Volatile True
Dim N As Long
For N = 1 To 13
Dim BalanceCheck As Double
Dim CellRow As Integer
Dim Current_cell As Range
Sheets(N).Activate
Set Current_cell = Columns(3).Find("Total Money left in bank account", LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False)
CellRow = Current_cell.Row
BalanceCheck = 0
While BalanceCheck = 0
If Cells(CellRow  1, "F").Value = "" Then
CellRow = CellRow  1
Else
BalanceCheck = Cells(CellRow  1, "F").Value
End If
Wend
FindBalance = BalanceCheck
Next N
End Function
