如何通过VBA在各工作表中填充Ticker列及后续相关计算列？

Hey there, let's work through why your Ticker column isn't populating and get the rest of those stock metrics working properly too. The core issue is your code calculates the Ticker value but never writes it to the worksheet, plus there are some timing and logic gaps in how you're handling prices and volume.

Key Issues in Your Original Code #

• You never actually output the Ticker value to column I (the header you created) — you define the variable but don't write it to a cell
• The open_price variable stays stuck at 0; you don't capture the first opening price for each new Ticker
• Price change/percent change logic is out of order, so calculations won't run as intended
• You declared Ticker_volume but aren't using it to accumulate volume for each Ticker
• The output row variable j is declared but never initialized or used to track where to write each Ticker's summary

Corrected VBA Code #

Option Explicit
Sub Stock_market()
    'Declare and set worksheet
    Dim ws As Worksheet
    'Loop through all stocks for one year
    For Each ws In Worksheets
        'Create the column headings
        ws.Range("I1").Value = "Ticker"
        ws.Range("J1").Value = "Yearly Change"
        ws.Range("K1").Value = "Percent Change"
        ws.Range("L1").Value = "Total Stock Volume"
        ws.Range("P1").Value = "Ticker"
        ws.Range("Q1").Value = "Value"
        ws.Range("O2").Value = "Greatest % Increase"
        ws.Range("O3").Value = "Greatest % Decrease"
        ws.Range("O4").Value = "Greatest Total Volume"
        
        'Define variables
        Dim Ticker As String
        Dim Ticker_volume As Double
        Dim open_price As Double
        Dim close_price As Double
        Dim price_change As Double
        Dim price_change_percent As Double
        
        'Set initial values
        Ticker = ""
        Ticker_volume = 0
        'j tracks which row we write summary data to (starts under header row)
        Dim j As Long
        j = 2
        Dim Lastrow As Long
        Dim i As Long
        Lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
        'Initialize open_price with the first stock's opening price
        open_price = ws.Cells(2, 3).Value
        
        'Loop through each row of stock data
        For i = 2 To Lastrow
            'Accumulate total volume for current Ticker
            Ticker_volume = Ticker_volume + ws.Cells(i, 7).Value
            
            'Check if we've reached the end of the current Ticker's data
            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then
                'Set the current Ticker symbol
                Ticker = ws.Cells(i, 1).Value
                
                'Get closing price for the Ticker's last day
                close_price = ws.Cells(i, 6).Value
                
                'Calculate yearly change and percent change
                price_change = close_price - open_price
                'Avoid division by zero if open price is 0
                If open_price <> 0 Then
                    price_change_percent = (price_change / open_price) * 100
                Else
                    price_change_percent = 0 'Adjust this handling if needed
                End If
                
                'Write all summary data to the worksheet
                ws.Cells(j, 9).Value = Ticker 'Column I = Ticker
                ws.Cells(j, 10).Value = price_change 'Column J = Yearly Change
                ws.Cells(j, 11).Value = price_change_percent 'Column K = Percent Change
                ws.Cells(j, 12).Value = Ticker_volume 'Column L = Total Volume
                
                'Format percent change as percentage (optional but user-friendly)
                ws.Cells(j, 11).NumberFormat = "0.00%"
                
                'Reset variables for the next Ticker
                Ticker_volume = 0
                'Set open_price to the next Ticker's opening price (if available)
                If i + 1 <= Lastrow Then
                    open_price = ws.Cells(i + 1, 3).Value
                End If
                
                'Move to the next row for the next Ticker's summary
                j = j + 1
            End If
        Next i
    Next ws
End Sub

What Changed & Why #

1. Added Output Logic: We use variable j to track the row for writing summaries, and explicitly write Ticker (and other metrics) to columns I-L — this fixes your empty Ticker column issue.
2. Fixed Open Price Capture: We initialize open_price with the first stock's opening price, then update it to the next Ticker's opening price as we finish each Ticker's data.
3. Volume Accumulation: We now add each row's volume to Ticker_volume every loop, then reset it when moving to a new Ticker.
4. Corrected Calculation Order: We calculate price change and percent change only when we reach the end of a Ticker's data, ensuring we have the correct opening and closing prices.
5. Division by Zero Handling: Added a check to avoid runtime errors if a Ticker has an opening price of 0.

This code will now populate your Ticker column, plus calculate and write Yearly Change, Percent Change, and Total Stock Volume for each Ticker. You can build on this later to add the "Greatest % Increase/Decrease/Total Volume" metrics by tracking those values as you loop through each Ticker's summary.

内容的提问来源于stack exchange，提问作者David H