如何通过重复下移6行的字符串源数据生成多个Sparklines迷你图

你当前的代码中SourceData固定为"d6:s6"，无法实现逐次下移6行的需求。问题出在你尝试的"d5+6:s5+6"语法——VBA不会自动解析字符串内的算术运算，必须通过字符串拼接动态生成源数据地址。

核心修正步骤 #

将代码中固定源数据的行：

Range(Cells(5 + incre, 23)).SparklineGroups.Add Type:=xlSparkLine, SourceData:="d6:s6"

替换为动态拼接地址的代码：

Range(Cells(5 + incre, 23)).SparklineGroups.Add Type:=xlSparkLine, SourceData:="d" & (5 + incre) & ":s" & (5 + incre)

修正后的完整代码 #

Sub High()
'
' High Macro
' Graph high temps
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Dim counter As Integer
    Dim incre As Integer
    
    incre = 0
    
    For counter = 1 To 50
    
    Range(Cells(5 + incre, 23), Cells(10 + incre, 29)).Select
    Selection.Merge
    ' 动态生成下移6行的源数据地址
    Range(Cells(5 + incre, 23)).SparklineGroups.Add Type:=xlSparkLine, SourceData:="d" & (5 + incre) & ":s" & (5 + incre)
    Selection.SparklineGroups.Item(1).SeriesColor.Color = 9592887
    Selection.SparklineGroups.Item(1).SeriesColor.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Negative.Color.Color = 208
    Selection.SparklineGroups.Item(1).Points.Negative.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Markers.Color.Color = 208
    Selection.SparklineGroups.Item(1).Points.Markers.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Highpoint.Color.Color = 208
    Selection.SparklineGroups.Item(1).Points.Highpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.Color = 208
    Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.Color = 208
    Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.TintAndShade = 0
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.Color = 208
    Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.TintAndShade = 0
    
    incre = incre + 6
    
    Next counter
    
End Sub

逻辑说明 #

• incre初始值为0，第一次循环时源数据地址为d5:s5（5+0=5）
• 每次循环结束后incre +=6，下一次循环的源数据地址自动变为d11:s11（5+6=11），以此实现每次下移6行的效果

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