Excel VBA循环工作表时动态设置图表X轴标签范围问题

Let's tackle the syntax issue you're facing with setting the X-axis labels dynamically across worksheets. The core problem here is how you're referencing the worksheet name in the XValues string—plus a small oversight in how you're finding the last row of data.

The Exact Fix for XValues #

Your static version works because you explicitly use the worksheet name wrapped in single quotes. When looping through worksheets dynamically, you need to replicate that structure correctly:

Instead of this broken line:

.SeriesCollection(1).XValues = ("='" & Sht & "'!$AA$8:$AA" & Lastrowdata)

Use this corrected code:

.SeriesCollection(1).XValues = "='" & Sht.Name & "'!$AA$8:$AA" & Lastrowdata

Why This Works:

• Sht is a Worksheet object—while VBA might implicitly use its Name property when concatenating, explicitly calling Sht.Name is far more reliable (especially if your worksheet names have spaces, special characters, or if you tweak the code later).
• The single quotes around the worksheet name ensure Excel parses the reference correctly, matching the working static format you tested.

Bonus Fix: Get Last Row from the Correct Worksheet #

Your current code for finding Lastrowdata uses [AB:AB], which references the active worksheet, not the one you're looping through. To ensure you're using local data from the current sheet in the loop, update that line to:

Lastrowdata = Sht.Range("AB:AB").Find("*", , xlValues, , xlByRows, xlPrevious).Row

Full Corrected Macro #

Here's the complete updated code with all fixes applied (I also adjusted the chart location range to reference the current worksheet, avoiding another potential active-sheet bug):

Sub Test()
    Dim Sht As Worksheet
    Dim rng As Range, rngChart As Range, XLabelrng As Range
    Dim Lastrowdata As Long
    Dim cht As Object
    
    For Each Sht In Worksheets
        'Local Management Details Mapping (graph data)
        With Sht
            .Range("AA8") = "=IFERROR(IF(VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)=0,"""",""FX""),"""")"
            .Range("AB8") = "=IFERROR(IF(VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)=0,"""",VLOOKUP(""FX Allocation & Hedging"",B:C,2,FALSE)),"""")"
            .Range("AA9:AA18") = "=IF(E9=""Yield Curve"",""YC"",IF(E9=""Asset Allocation"",""A. Alloc"",IF(E9=""Security Selection"",""Sec Sel"",IF(E9=""Leverage"",""Lev"",IF(E9=""Intra-Day"",""Intra"",IF(E9=""Pricing Differences"",""Pric"",IF(E9=""Exclusions"",""Exc"",IF(E9=""Interest Rate Derivative Basis"",""IRD"",IF(E9=""Implied Volatility"",""Vol"",IF(E9=""Mortgage"",""Mtg"",IF(E9=""Residual"",""Res"",IF(E9=""Others"",""Others"",""Others""))))))))))))"
            .Range("AB9:AB18") = "=IF(I9="""","""",I9)"
            '.Range("AA8:AB18").Font.Color = vbWhite
        End With
        
        'Your data range for the chart and x-axis labels
        Lastrowdata = Sht.Range("AB:AB").Find("*", , xlValues, , xlByRows, xlPrevious).Row
        Set rng = Sht.Range("AB8:AB" & Lastrowdata)
        
        'Chart Location (now tied to current worksheet)
        Set rngChart = Sht.Range("K9:W18")
        
        'Create a chart (style,XlChartType,Left,Top,Width,Height,NewLayout)
        Set cht = Sht.Shapes.AddChart2(203, xlColumnClustered, 1, 1, 1, 1, False)
        
        'Chart setup
        With cht.Chart
            .SetSourceData Source:=rng
            .SeriesCollection(1).XValues = "='" & Sht.Name & "'!$AA$8:$AA" & Lastrowdata
            .HasTitle = False
            .HasLegend = False
            .Axes(xlValue).MajorUnit = 50
        End With
        
        'Chart location
        With cht
            .Left = rngChart.Left
            .Top = rngChart.Top
            .Width = rngChart.Width
            .Height = rngChart.Height
        End With
    Next Sht
End Sub

内容的提问来源于stack exchange，提问作者Ryan S