CODEWITHSUNDEEP
vbaexcel
Gjovis
Gjovis

Reputation: 53

Loan Amortization Table Line Chart

I am trying to create a chart of a loan amortization table that have been created in vba excel. I have used the following code: 

 Private Sub CommandButton4_Click()

'Create a line chart of the loan amortization table
Dim pChart As Chart
Dim pRange As Range
Dim axisRange As Range

'Set the range to the cells C8:H23
Set pRange = Range(Cells(8, 4), Cells(23, 8))

'Set the x-axis range to the cells C9:C23
Set axisRange = Range(Cells(9, 3), Cells(23, 9))

'Create the chart as a line chart
Set pChart = Charts.Add
pChart.HasLegend = False
pChart.ChartType = xlLine
pChart.SetSourceData pRange
pChart.PlotBy = xlColumns

'Set the x-axis chart to the created address
pChart.SeriesCollection(1).XValues = "=" & axisRange.Address(False, False, xlA1, xlExternal)

'Choose the location of the chart
pChart.Axes(xlCategory).HasMajorGridlines = True
pChart.Location xlLocationAsObject, Sheet1.Name
Sheet1.ChartObjects.Left = 125
Sheet1.ChartObjects.Top = 250
Cells(1, 1).Select

End Sub

However, when the chart is created, a lot of numbers are added in the x-axis. I have added a screenshot of it. Do anyone know how to change, so only the year number shows on the x-axis? And also, how to make the names of the lines visible? Line Chart

Upvotes: 1

Views: 118

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

Typo:

'Set the x-axis range to the cells C9:C23
Set axisRange = Range(Cells(9, 3), Cells(23, 9))

You've really set the axis range to C9:I23. This should be

Set axisRange = Range(Cells(9, 3), Cells(23, 3))

You've really set the axis range to C9:I23. Pro tip: if you know the whole range's address, you can use it:

Set axisRange = Range("C9:C23")

I've cleaned up the code, assuming you want to use the active worksheet:

Private Sub CommandButton4_Click()

  'Create a line chart of the loan amortization table
  Dim pChart As Chart
  Dim pRange As Range
  Dim axisRange As Range

  'Set the range to the cells C8:H23
  Set pRange = ActiveSheet.Range("C8:H23")

  'Set the x-axis range to the cells C9:C23
  Set axisRange = ActiveSheet.Range("C9:C23")

  'Create the chart as a line chart
  Set pChart = ActiveSheet.Shapes.AddChart
  pChart.HasLegend = False
  pChart.ChartType = xlLine
  pChart.SetSourceData pRange
  pChart.PlotBy = xlColumns

  'Set the x-axis chart to the created address
  pChart.SeriesCollection(1).XValues = axisRange

  'Choose the location of the chart
  pChart.Axes(xlCategory).HasMajorGridlines = True
  pChart.Parent.Left = 125
  pChart.Parent.Top = 250
  ActiveSheet.Cells(1, 1).Select

End Sub

Upvotes: 1

Related Questions