Home Making a specific cell in each sheet returns a value above it and stay volatile
Reply: 0

Making a specific cell in each sheet returns a value above it and stay volatile

user1956
1#
user1956 Published in May 21, 2018, 9:16 am

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.327297 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO