0

I'm trying to modify an existing VBA code (Excel) which makes Chart graphs, and make it more flexible.

I know that the following pieces of code do basically the same thing:

Range(Cells(12, 2), Cells(15, 2)).Select

is more or less identical to: 

Range("B12:B15").Select

My goal was to have a Graph, which is representing the flexible count of rows.

So I have changed the existing code:

ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range("B12:B200"), PlotBy:=xlColumns

to 

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row    
ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range(Cells(12, 2), Cells(LastRow, 2)), PlotBy:=xlColumns

Now, whenever I execute the code, I receive:

Run-time error '1004': Application-defined or object-defined error

The LastRow variable is not the problem: I have the same result if I replace it with 200.

What am I doing wrong?

Cheers

Peter

Community
  • 1
  • 1
PeterKallus
  • 1
  • 1
  • 1
  • 1
  • 2
    Whenver you use `sheetname.Range(Cells(a,b), Cells(a2,b2))` you should always qualify each `Cells()` with the sheet reference: otherwise, `Cells()` will always refer to the ActiveSheet, which may not be what you intended. – Tim Williams Apr 05 '13 at 16:31

4 Answers4

0
Worksheets("Log-Data").Activate

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row    
ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range(Cells(12, 2), Cells(LastRow, 2)), PlotBy:=xlColumns

Worksheets("NAME_CHEET_CHART").Activate
SandPiper
  • 2,816
  • 5
  • 30
  • 52
Aleksei Subbotin
  • 1
  • 1
-1

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

and

LastRow = ActiveSheet.UsedRange.Rows.Count

are the same.

As far as the actual error goes, it looks as if you have no active chart object when the code is run. So ActiveChart. returns an object error.

Try this:

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Log-Data").Range(Cells(12, 2), Cells(LastRow, 2)), PlotBy:=xlColumns
James Chevalier
  • 10,604
  • 5
  • 48
  • 74
273K Kool
  • 9
  • 1
-1

I tried the following and it works (adding a period before Cells word)

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count

With Sheets("Log-Data")
Set Myrange = .Range(.Cells(12, 2), .Cells(LastRow, 2))
End With

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Myrange, PlotBy:=xlColumns
ljacqu
  • 2,132
  • 1
  • 17
  • 21
user3962941
  • 1
-3

Try below and let me know 

Dim LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count

With Sheets("Log-Data")
Set Myrange = .Range(Cells(12, 2), Cells(LastRow, 2))
End With

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Myrange, PlotBy:=xlColumns
Sanjay Narayan
  • 1