excel - VBA代码不会从折线图中的定义范围添加类别

Question

我正在尝试通过使用 VBA 创建一个折线图，我通过使用附加的代码来做到这一点，我在 rng 中列出了我想要的类别，但是它不会自动选择这个作为类别，我想要第 4 列,5 显示在图表上，3 显示类别。

Sub CreateGBPChart()
'PURPOSE: Create a chart (chart dimensions are not required)

Dim rng As Range
Dim cht As Object
Dim wks As Worksheet, wks2 As Worksheet

Set wks = Sheet2
Set wks2 = Sheet5

'data range for the chart
lastrow = wks.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
  Set rng = Range(wks.Cells(10, 3), wks.Cells(lastrow, 5))


'Create a chart
  Set cht = wks2.ChartObjects.Add( _
    Left:=wks2.Range("K18").Left, _
    Width:=480, _
    Top:=wks2.Range("K18").Top, _
    Height:=200)

' chart data
  cht.Chart.SetSourceData Source:=rng
  'cht.Chart.Axes(xlCategory).CategoryNames = Range(wks.Cells(10, 3), wks.Cells(lastrow, 3))

'Determine the chart type
  cht.Chart.ChartType = xlXYScatterLines

'chart  title
  cht.Chart.HasTitle = True

'Change  title
  cht.Chart.ChartTitle.Text = "GBP Price"

'Remove Legend
  'cht.Legend.Delete
cht.Chart.Parent.Name = "GBPTable"

'adjust the min val
cht.Axes(xlValue).MinimumScale = 1
'format fontsize'
cht.Chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8

'cht.Chart.chartSeriesCollection(1).Line.Width = 4
'cht.Chart.chartSeriesCollection(2).Format.Line.Width = 4

End Sub

标签： excelvbagraphcategorieslinechart

Answer 1

尝试这个：

' cht.Chart.SetSourceData Source:=rng
  cht.Chart.SetSourceData Source:=wks.Range(wks.Cells(10, 4), wks.Cells(LastRow, 5))

' Add lines
  cht.Chart.SeriesCollection(1).XValues = wks.Range(wks.Cells(10, 3), wks.Cells(LastRow, 3))
  cht.Chart.SeriesCollection(2).XValues = wks.Range(wks.Cells(10, 3), wks.Cells(LastRow, 3))