遍历全工作簿的VBA脚本仅格式化首个工作表问题排查求助

Hey John, let's break down why your formatting only applies to the first worksheet and fix those issues step by step!

Core Issue: Formatting Doesn't Target the Current Worksheet #

The biggest problem is in your column formatting section. When you write:

Set colJ = Range("J2", Range("J2").End(xlDown))

You're not specifying which worksheet this Range belongs to. By default, VBA uses the active worksheet (which stays as the first worksheet you process through the loop). So all your formatting logic is actually modifying the first sheet's column J, not the current ws you're iterating over.

Fix it by explicitly binding the range to the current worksheet:

Set colJ = ws.Range("J2", ws.Range("J2").End(xlDown))

Secondary Issue: Incorrect Last Row Calculation #

You set lastrow = ActiveSheet.UsedRange.Rows.Count outside of your worksheet loop. This means every subsequent worksheet uses the row count from the first active sheet, which can cause incomplete loops or incorrect data processing for other sheets.

Move this line inside the For Each ws loop to get the correct last row for each sheet:

For Each ws In Worksheets
    lastrow = ws.UsedRange.Rows.Count ' Now gets the row count for the current sheet
    ' Rest of your sheet processing code...
Next ws

Bonus Fixes for Robustness #

1. Misplaced firstprice Declaration:
   You declared Dim firstprice As Boolean inside the For i loop, which resets the variable to False every row iteration. Move this declaration to inside the worksheet loop (before the row loop) to keep track of the opening price correctly:

   For Each ws In Worksheets
       Dim firstprice As Boolean
       firstprice = False
       lastrow = ws.UsedRange.Rows.Count
       ' ... rest of code
   

2. Incorrect Percentage Change Calculation:
   Your code calculates yrvar = (yrclose - yropen) / yrclose — this divides by the closing price instead of the opening price, which is the standard for yearly percentage change. I fixed this in the full code below, but feel free to revert if it was intentional.

3. Missing Volume Accumulation:
   You're only capturing the last row's volume for each ticker instead of summing all rows. The full code below adds stock_vol = stock_vol + ws.Cells(i, 7).Value to fix this.

Corrected Full Code #

Sub tickerdata_all_ws()
    ' Define variables
    Dim ws As Worksheet
    Dim ticker As String
    Dim stock_vol As Long
    Dim yrclose As Double
    Dim yrchange As Double
    Dim yrvar As Double
    Dim i As Long
    Dim sumrow As Integer
    Dim lastrow As Long
    
    For Each ws In Worksheets
        ' Get last row of current worksheet
        lastrow = ws.UsedRange.Rows.Count
        
        ' Create column headers
        ws.Range("H1").Value = "Ticker"
        ws.Range("J1").Value = "Yearly Change"
        ws.Range("K1").Value = "Percent Change"
        ws.Range("L1").Value = "Total Stock Volume"
        
        ' Initialize summary row and tracking variables
        sumrow = 2
        stock_vol = 0
        Dim firstprice As Boolean
        firstprice = False
        
        ' Loop through each data row
        For i = 2 To lastrow
            ' Capture opening price for new ticker
            If firstprice = False Then
                Dim yropen As Double
                yropen = ws.Cells(i, 3).Value
                firstprice = True
            End If
            
            ' Accumulate total stock volume
            stock_vol = stock_vol + 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
                ticker = ws.Cells(i, 1).Value
                yrclose = ws.Cells(i, 6).Value
                yrchange = yrclose - yropen
                
                ' Calculate percentage change (avoid division by zero)
                If yropen <> 0 Then
                    yrvar = (yrclose - yropen) / yropen
                Else
                    yrvar = 0
                    yrchange = 0
                End If
                
                ' Write summary data
                ws.Cells(sumrow, 9).Value = ticker
                ws.Cells(sumrow, 10).Value = yrchange
                ws.Cells(sumrow, 11).Value = yrvar
                ws.Cells(sumrow, 12).Value = stock_vol
                
                ' Reset for next ticker
                sumrow = sumrow + 1
                stock_vol = 0
                firstprice = False
            End If
        Next i
        
        ' Format percentage column
        ws.Range("K:K").NumberFormat = "0.0%"
        
        ' Format yearly change column colors
        Dim colJ As Range
        Dim Cell As Range
        Set colJ = ws.Range("J2", ws.Range("J2").End(xlDown))
        For Each Cell In colJ
            If Cell.Value > 0 Then
                Cell.Interior.ColorIndex = 50
                Cell.Font.ColorIndex = 2
            ElseIf Cell.Value < 0 Then
                Cell.Interior.ColorIndex = 30
                Cell.Font.ColorIndex = 2
            Else
                Cell.Interior.ColorIndex = xlNone
            End If
        Next
    Next ws
End Sub

内容的提问来源于stack exchange，提问作者John Santoyo