Charting with VBA - wanting to use .End(xlDown) but getting method failure

markdarby

New Member
Joined
Jun 24, 2018
Messages
1
Hi All,
I've read more web posts and tried more things that I can shake a stick at... none seem to work. I've hard coded the name of the data sheet that I'm using for now... but will revert to using the parameter that I pass to the subroutine later.

Sub CreateGraphs(ProjectToGraph As String)

Dim MyChart As Chart
Dim DataRange As Range


ProjectToGraph = "ARLG"

Worksheets("ARLG").Activate

With Worksheets("ARLG").Shapes.AddChart2(227, xlLineStacked, 30, 30, 600, 400).Chart

'===================================================================================================

' This line works without any issue. Since I'll never know how many rows are in the data field, I would have to edit the range every time I created the chart.
'.SetSourceData Range("ARLG_Data!$A$1:$C$35")

' Wanting to dynamically set the last row.... but this fails every time with an error: "Method 'SetSourceData' of object'_Chart' failed"
.SetSourceData Range("ARLG_Data!$A$1:$C$1"), Range("ARLG_Data!$A$1:$C$1").End(xlDown)




'=================================================================================================== .HasTitle = True
.ChartTitle.Text = ProjectToGraph
.SetElement (msoElementLegendBottom)
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Labor Hours"
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "dd mmm"
End With

' Need to add a vertical line for the date....

End Sub


I'd be grateful for help that anyone can provide.

Mark
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,253
Office Version
2013
Platform
Windows
Code:
.SetSourceData sheets("ARLG_Data").Range("$A$1", Sheets("ARLG_Data").Cells(Rows.Count, 3).End(xlUp))
Or if you have other data below the range you want to use, then

Code:
.SetSourceData sheets("ARLG_Data").Range("$A$1", Sheets("ARLG_Data").Cells(1, 3).End(xlDown))
 
Last edited:

Forum statistics

Threads
1,081,536
Messages
5,359,372
Members
400,525
Latest member
swwber

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top