CODEWITHSUNDEEP
excelvbadynamiccellworksheet
Manu221
Manu221

Reputation: 41

Dynamically Calculating Values when cell changes VBA

So I am running a spread sheet that has a live data feed of stock prices. I have written this into the worksheet so that when the price updates, the value of the position updates and calculates the new total value of the portfolio.

So I have 5 main headings being Players, Stock Code, Units, Current Price, Value.

Now this code works, but partially. When the price updates, it doesn't automatically update the value unless I go back and forth from the cell. 

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim i As Integer, j As Integer, m As Integer, t As Integer
Dim firstrow As Integer, lastrow As Integer
Dim fr As Integer, lr As Integer, lr2 As Integer
Dim sum As Double
Dim name As Range, stock As Range, value As Range, units As Range, cp As Range

With ActiveSheet.Range("A:Z")

    Set stock = .find(what:="Stock Code", After:=.Cells(.Cells.count), LookIn:=xlValues, _
                Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Set value = .find(what:="Value", After:=.Cells(.Cells.count), LookIn:=xlValues, _
                Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Set name = .find(what:="Players", After:=.Cells(.Cells.count), LookIn:=xlValues, _
                Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Set units = .find(what:="Units", After:=.Cells(.Cells.count), LookIn:=xlValues, _
                Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Set cp = .find(what:="Current Price", After:=.Cells(.Cells.count), LookIn:=xlValues, _
                Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    fr = stock.Row + 1
    lr = .Cells(.Rows.count, stock.Column).End(xlUp).Row

    If Cells(name.Row + 1, name.Column) = "" Then
        End
    End If

    For i = fr To lr

'checks to ensure that there is a player in the spreadsheet
        If Cells(i, name.Column) <> "" Then
         m = i
        End If

'each player has a total row. The word Total identifies the last row for a player
        For j = m To lr
            If Cells(j, stock.Column) = "TOTAL" Then
                lr2 = j
                Exit For
            End If
        Next j

'checks the range where there are stock codes in the sheet, and when the current price updates it
                If Not Intersect(target, target.Worksheet.Range(.Cells(m, cp.Column), .Cells((lr2 - 1), cp.Column))) Is Nothing Then
                    For t = m To (lr2 - 1)
                        Cells(t, value.Column) = Cells(t, cp.Column) * Cells(t, units.Column)
                    Next t
                End If

'checks to see if there has been a change in the value column for each stock and recalculates the new total
            sum = 0
                If Not Intersect(target, target.Worksheet.Range(.Cells(m, value.Column), .Cells((lr2 - 1), value.Column))) Is Nothing Then
                    For t = m To (lr2 - 1)
                        sum = sum + Cells(t, value.Column)
                    Next t
                    Cells(lr, value.Column) = sum
                End If

    Next i

End With

End Sub

I'm just not sure why when the current price updates, i need to go into the value cell and out again before it actually updates.

Appreciate any feedback.

Upvotes: 1

Views: 106

Answers (1)

Manu221
Manu221

Reputation: 41

Sorry I figured it out. I was running it under Private Sub Worksheet_SelectionChange(ByVal target As Range) rather than Private Sub Worksheet_Change(ByVal target As Range)

Upvotes: 1

Related Questions