How to reference a cell value each day

[ddub25]

In A1 I have a balance that is constantly fluctuating depending on transactions that I process on other parts of the spreadsheet.

Is it possible to, lets say, have B1 read this value on Monday and fix/freeze at this value, and then B2 do they same for Tuesday, B3 for Wednesday and so forth?

The reason why, is that I want to output the changes in balance over a period of time into a chart.

Ideally I would have each new change in balance recorded rather than just recording at the start of each new day, but I imagine this might be something to do in Visual Basic. If it is, can anyone point me to a good resource on Visual Basic for doing this sort of thing?

Dan

 

[bensonsearch]

Hi,
In the vba editor go to the sheet1 module not a new one. insert code as such (changing as needed)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
         ' Place your code here.

thisworkbook.sheets("Sheet1").range("B"&range).value =  thisworkbook.sheets("Sheet1").range("A1").value
        
       
    End If

End Sub

can put code to check if b2 or b3 has a value in the workbook open event in workbook module and change the B value.
eg:

Private Sub Workbook_Open()
if thisworkbook.sheets("Sheet1").range("B2").value <> "" then
range = 3
else 
range = 2
end if
 
End Sub

range would be a global variable or can use a non changing cell on worksheet. you would need to modify the above, let me know if you wish more help