我对VBA比较陌生 - 即使我不理解它也倾向于使用有效的东西我害怕！
我试图为工作表中的每一行数据生成单独的散点图。 我可以配置图表等，但让它读取每一行并正确迭代是我的！
这是我目前所拥有的：
Private Sub CommandButton1_Click() ActiveWorkbook.Charts.Add Dim i As Integer For i = 2 To WS.Range("A65536").End(xlUp).Row With ActiveWorkbook.ActiveChart 'Data? .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).Name = "Progress" .SeriesCollection(1).XValues = "=Sheet2!$B$1:$J$1" .SeriesCollection(1).Values = "=Sheet2!$B$" & i & ":$J$" & i 'Titles .HasTitle = True .ChartTitle.Characters.Text = "valuefromcellN2?" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade" .Axes(xlCategory).HasMajorGridlines = True 'Formatting .Axes(xlCategory).HasMinorGridlines = False .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).HasMinorGridlines = False .HasLegend = False End With Next End Sub当我运行这个，我得到一个运行时错误424 - 所需的对象。 我的问题是：
我是否以正确的方式去解决这个问题？ 我错过了什么导致此运行时错误？ 我如何从cellN2获取价值？ 实际显示的价值，而不是文字？谢谢你的帮助！
山姆
编辑：
感谢您的帮助 - 我想我现在已经正确更新了它（适用于所有标题并创建新图表！）
Private Sub CommandButton1_Click() ActiveWorkbook.Charts.Add Dim ws As Worksheet Set ws = tracker Dim i As Long For i = 2 To ws.Range("A65536").End(xlUp).Row Charts.Add With ActiveWorkbook.ActiveChart 'Data? .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).Name = "Progress" .SeriesCollection(1).XValues = "=tracker!$B$1:$J$1" .SeriesCollection(1).Values = "=tracker!$B$" & i & ":$J$" & i 'Titles .HasTitle = True .ChartTitle.Characters.Text = Range("N" & i).Value .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade" .Axes(xlCategory).HasMajorGridlines = True 'Formatting .Axes(xlCategory).HasMinorGridlines = False .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).HasMinorGridlines = False .HasLegend = False End With Next End SubI'm relatively new to VBA - tend to just go with stuff that works even if I don't understand it I'm afraid!
I'm trying to produce individual scatter graphs for each row of data from a worksheet. I'm OK with configuring the graph etc, but getting it to read each row and iterate properly is beyond me!
This is what I've got currently:
Private Sub CommandButton1_Click() ActiveWorkbook.Charts.Add Dim i As Integer For i = 2 To WS.Range("A65536").End(xlUp).Row With ActiveWorkbook.ActiveChart 'Data? .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).Name = "Progress" .SeriesCollection(1).XValues = "=Sheet2!$B$1:$J$1" .SeriesCollection(1).Values = "=Sheet2!$B$" & i & ":$J$" & i 'Titles .HasTitle = True .ChartTitle.Characters.Text = "valuefromcellN2?" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade" .Axes(xlCategory).HasMajorGridlines = True 'Formatting .Axes(xlCategory).HasMinorGridlines = False .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).HasMinorGridlines = False .HasLegend = False End With Next End SubWhen I run this, I get a runtime error 424 - object required. My questions are:
Am I going about this in roughly the right way? What have I missed out to cause this runtime error? How do I get the valuefromcellN2? to actually show the value, not text?Thanks for any help!
Sam
EDIT:
thanks for the help - I think I've updated it correctly now (it works with all titles and creates new graphs!)
Private Sub CommandButton1_Click() ActiveWorkbook.Charts.Add Dim ws As Worksheet Set ws = tracker Dim i As Long For i = 2 To ws.Range("A65536").End(xlUp).Row Charts.Add With ActiveWorkbook.ActiveChart 'Data? .ChartType = xlXYScatter .SeriesCollection.NewSeries .SeriesCollection(1).Name = "Progress" .SeriesCollection(1).XValues = "=tracker!$B$1:$J$1" .SeriesCollection(1).Values = "=tracker!$B$" & i & ":$J$" & i 'Titles .HasTitle = True .ChartTitle.Characters.Text = Range("N" & i).Value .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade" .Axes(xlCategory).HasMajorGridlines = True 'Formatting .Axes(xlCategory).HasMinorGridlines = False .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).HasMinorGridlines = False .HasLegend = False End With Next End Sub最满意答案
Q1我是否以正确的方式解决这个问题？
是的，你是。 你只需要解决一些问题。
Q2我错过了什么导致此运行时错误？
你得到那个错误是因为你没有声明ws对象。 我稍后在下面的代码中解释了如何解决这个问题。
Q3如何从cellN2获取价值？ 实际显示的价值，而不是文字？
为了得到特定单元格的值，你可以说Range("A1").Value在你的情况下，它的Range("A1").Value是Range("N2").Value
其他很少的东西
使用Excel行时，请避免使用Integer 。 使用Long 。 发布Excel 2007后，行数增加， Integer可能无法容纳更大的值。
避免使用像65536这样的硬编码值。 请参阅如何查找最后一行。
码
我已经评论了代码，所以你不应该有理解它的问题。 但是，如果你这样做，然后只是问。
这是你正在尝试（ 未经测试 ）？
Private Sub CommandButton1_Click() Dim ws As Worksheet Dim i As Long, LRow As Long Dim chrt As Chart '~~> This is where we will have the chart '~~> We declare the object here. Change as applicable Set ws = ThisWorkbook.Sheets("Sheet2") With ws '~~. Find the last row in Col A LRow = .Range("A" & .Rows.Count).End(xlUp).Row '~~> Add Chart Set chrt = .Shapes.AddChart.Chart With chrt '<~~ Work with chart and set it's parameters .ChartType = xlXYScatter .HasTitle = True .ChartTitle.Characters.Text = ws.Range("N2").Value '<~~ Set the Value here .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade" .Axes(xlCategory).HasMajorGridlines = True 'Formatting .Axes(xlCategory).HasMinorGridlines = False .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).HasMinorGridlines = False .HasLegend = False n = 1 '~~> Add series and set it For i = 2 To LRow .SeriesCollection.NewSeries .SeriesCollection(n).Name = "Progress" .SeriesCollection(n).XValues = "=Sheet2!$B$1:$J$1" .SeriesCollection(n).Values = "=Sheet2!$B$" & i & ":$J$" & i n = n + 1 Next i End With End With End SubQ1 Am I going about this in roughly the right way?
Yes you are. You need to just iron out few issues.
Q2 What have I missed out to cause this runtime error?
You are getting that error because you have not declared the ws object. I have explained it later in the code below on how to go about it.
Q3 How do I get the valuefromcellN2? to actually show the value, not text?
To get the value from a particular cell, you can say Range("A1").Value so in your case it will be Range("N2").Value
Few other things
When working with Excel rows, avoid the use of Integer. Use Long. Post Excel 2007, the number of rows have increased and the Integer may not be able to accommodate bigger values.
Avoid using hard coded values like 65536. See This on how to find the last row.
Code
I have commented the code so you shouldn't have a problem understanding it. But if you do then simply ask.
Is this what you are trying (Untested)?
Private Sub CommandButton1_Click() Dim ws As Worksheet Dim i As Long, LRow As Long Dim chrt As Chart '~~> This is where we will have the chart '~~> We declare the object here. Change as applicable Set ws = ThisWorkbook.Sheets("Sheet2") With ws '~~. Find the last row in Col A LRow = .Range("A" & .Rows.Count).End(xlUp).Row '~~> Add Chart Set chrt = .Shapes.AddChart.Chart With chrt '<~~ Work with chart and set it's parameters .ChartType = xlXYScatter .HasTitle = True .ChartTitle.Characters.Text = ws.Range("N2").Value '<~~ Set the Value here .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade" .Axes(xlCategory).HasMajorGridlines = True 'Formatting .Axes(xlCategory).HasMinorGridlines = False .Axes(xlValue).HasMajorGridlines = True .Axes(xlValue).HasMinorGridlines = False .HasLegend = False n = 1 '~~> Add series and set it For i = 2 To LRow .SeriesCollection.NewSeries .SeriesCollection(n).Name = "Progress" .SeriesCollection(n).XValues = "=Sheet2!$B$1:$J$1" .SeriesCollection(n).Values = "=Sheet2!$B$" & i & ":$J$" & i n = n + 1 Next i End With End With End Sub更多推荐
发布评论