Excel宏不会停在最后一行
Excel宏不会停在最后一行
提问于 2022-10-28 17:52:28
我有一个宏，它正在构建一个气泡图，对于动态范围中的每一行，它都在创建一个新的气泡图系列。我测试了最后一行的计算，即在工作表上手动查找实际的最后一行，并使用一个快速宏查找最后一行并在消息框中显示。因此，构建气泡图的宏是正确找到最后一行的。问题是，无论如何，宏都是在最后一行之后添加空白系列。宏将在最后一行之后添加10个泛型系列。
宏如下：
Sub bubble()
'
' bubble Macro for bubble chart
'
Dim Lastrow As Long, ws As Worksheet, wsRD As Worksheet, wsChart As Worksheet
Dim cht As ChartObject, currRow As Integer
Dim ch As Shape, SeriesNum As Integer
On Error GoTo ExitSub
For Each ws In ActiveWorkbook.Sheets
If Left(ws.Name, 12) = "Raw Data SEA" Then
Set wsRD = ws
End If
If Left(ws.Name, 10) = "SEA bubble" Then
Set wsChart = ws
End If
Next ws
Lastrow = wsRD.Cells(Rows.Count, 1).End(xlUp).Row
Set ch = wsChart.Shapes(1)
ch.Name = "SEACht"
SeriesNum = 1
For currRow = 2 To Lastrow
ch.Chart.SeriesCollection.NewSeries
ch.Chart.FullSeriesCollection(SeriesNum).Name = wsRD.Cells(currRow, 1)
ch.Chart.FullSeriesCollection(SeriesNum).XValues = wsRD.Cells(currRow, 2)
ch.Chart.FullSeriesCollection(SeriesNum).Values = wsRD.Cells(currRow, 4)
ch.Chart.FullSeriesCollection(SeriesNum).BubbleSizes = wsRD.Cells(currRow, 3)
SeriesNum = SeriesNum + 1
Next currRow
'Format Legend
ch.Chart.PlotArea.Select
ch.Chart.SetElement (msoElementLegendBottom)
ActiveWorkbook.Save
'Format X and Y axes
ch.Chart.Axes(xlCategory).Select
ch.Chart.Axes(xlCategory).MinimumScale = 0
ch.Chart.ChartArea.Select
ch.Chart.Axes(xlValue).Select
ch.Chart.Axes(xlValue).MinimumScale = 0
Application.CommandBars("Format Object").Visible = False
ActiveWorkbook.Save
' Format datalabels
ch.Chart.ApplyDataLabels
ch.Chart.FullSeriesCollection(1).DataLabels.Select
ch.Chart.FullSeriesCollection(1).HasLeaderLines = False
Application.CommandBars("Format Object").Visible = False
ActiveWorkbook.Save
' Add charttitle
'
ch.Chart.SetElement (msoElementChartTitleAboveChart)
ch.Chart.Paste
ch.Chart.ChartTitle.Text = _
"Properties operating exp - RSF and Building Age Factors"
ActiveWorkbook.Save
ExitSub:
End Sub提前感谢您的帮助。
检查最后一行calc是否确实找到了最后一行，以确保这不是问题所在。我试着再录一遍，看我有没有遗漏任何东西。我没有看到任何明显的改变。
回答 1
Stack Overflow用户
发布于 2022-10-28 18:17:41
对于评论来说太长了，可能不是问题的根源，但是NewSeries返回添加的系列，因此您可以这样做，跳过SeriesNum计数器：
Dim rw as Range
For currRow = 2 To Lastrow
Set rw = wsRD.Rows(currRow)
With ch.Chart.SeriesCollection.NewSeries
.Name = rw.Cells(1)
.XValues = rw.Cells(2)
.Values = rw.Cells(4)
.BubbleSizes = rw.Cells(3)
End With
Next currRow页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74239181
复制相关文章
相似问题
腾讯云开发者
