Why does this not work????

[nkemp]

Excel VBA can be soooo frustrating at times. And here is one. Why does this line not work? What do I need to do to fix it?:

ActiveChart.SetSourceData Source:=Sheets(2).range("B" & FindDataStart() & ":B" & FindEndRow())

The code runs fine as below (with the offending line commented out and a fixed value inserted):

Sub LineChartMaker2()
'
' ChartMaker2 Macro
'
    Sheets("Line Chart").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    'ActiveChart.SetSourceData Source:=Sheets(2).range("B" & FindDataStart() & ":B" & FindEndRow())
    ActiveChart.SetSourceData Source:=Sheets(2).range("B36" & ":B" & FindEndRow())

    ActiveChart.SeriesCollection(1).Name = "=""Laser"""
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "=""R-Eye"""
    'ActiveChart.SeriesCollection(2).Values = Sheets(2).range("t" & FindDataStart() & ":t" & FindEndRow())
    ActiveChart.SeriesCollection(2).Values = Sheets(2).range("t36" & ":t" & FindEndRow())
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "=""L-Eye"""
    'ActiveChart.SeriesCollection(3).Values = Sheets(2).range("q" & FindDataStart() & ":Q" & FindEndRow())
    ActiveChart.SeriesCollection(3).Values = Sheets(2).range("q36" & ":Q" & FindEndRow())
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).AxisGroup = 2
    
    With ActiveChart.Parent
         .Height = 325  ' resize
         .Width = 1000  ' resize
         .Top = 10      ' reposition
         .Left = 10    ' reposition
     End With
 
With ActiveChart
     'chart name Top
    .HasTitle = True
    .ChartTitle.Characters.Text = "Laser Vs MobileEye for file:  " & Sheets(2).range("b1").Value
     ActiveChart.ChartTitle.Font.Size = 20
     'X axis name
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Data Pairs"
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Size = 15
     'y-axis name (Left hand side)
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stripe Reflectivity"
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 15
      'Secondary Y axis (Right hand side)
    .Axes(xlValue, xlSecondary).HasTitle = True
    .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "MobileEye Values"
    .Axes(xlValue, xlSecondary).AxisTitle.Characters.Font.Size = 15
    
End With

End Sub


Function FindEndRow() As Integer
'Find end of data for chart
FindEndRow = Sheets(2).range("b65536").End(xlUp).Row

End Function


Function FindDataStart() As Integer
'Find start of data for chart
Dim rFound As range
On Error Resume Next          'cells(column, row)

    With Sheet2

        Set rFound = .range("B:B").find(What:="ave", After:=.Cells(2, 2), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    On Error GoTo 0

        If Not rFound Is Nothing Then Application.Goto rFound, True

    End With
    'MsgBox ActiveCell.Address
    FindDataStart = ActiveCell.Row + 1
    
    End Function

 

[RoryA]

Can you be more specific - what error do you get?

 

[nkemp]

Sorry about that ...

Run-time error'91':
Object variable or with block variable not set

What is confusing to me is why the FindEndRow() works in the equation but the FindDataStart does not

Thanks for helping!

 

[nkemp]

And FWIW ... a MsgBox with the equation "B" & FindDataStart() & ":B" & FindEndRow() outputs exactly what I want B36:B86

 

[RoryA]

The only reason I can see for you getting that error there would be if you had deselected the chart.

I note that your 'range' properties are not appearing as 'Range' in your code. Did you declare a variable or function called "range" somewhere?

 

[nkemp]

The only range declaration is in the FindDataStart function:

Dim rFound As range

To test the sub I'm only running the sub LineChartMaker2() so nothing else should be affecting it.

 

[RoryA]

Are you sure your second sheet has the codename Sheet2? What does:

Msgbox Sheet2.Name

return?

 

[nkemp]

Based on "Are you sure your second sheet has the codename Sheet2?" I changed the code to sheets(2) . But I still get the same error message. Note that the name for Sheets(2) changes for every run of the program I'm working on so I want to reference it by its location.

Edit: Running a s sheet2 or sheets(2) ... both ways generate the correct result for the start location in the column.

 

[nkemp]

Update: Changing:
Set rFound = .range("B:B")
to
Set rFound = .range("B2:B")

Eliminated the error message. I have no idea why though! ?????

Now to see if the functions work as needed.

Update: that broke something else. not finding the search value

 

[nkemp]

So I think I have it fixed ... but I don't know why this fixed it. Not knowing why makes it difficult to learn.

I added to the LineChartMaker2 sub:

Dim DataStart As Integer
DataStart = FindDataStart()

and then used DataStart rather than FindDataStart() as per:

ActiveChart.SetSourceData Source:=Sheets(2).range("B" & DataStart & ":B" & FindEndRow())

There are a few other code changes to FindDataStart() that make it work but did not fix the problem.


Here is the working code:

Sub LineChartMaker2()
'
' ChartMaker2 - Create a line chart, 2 axes, with a variable data start and end position
'
Dim DataStart As Integer
DataStart = FindDataStart()


    Sheets("Line Chart").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets(2).range("B" & DataStart & ":B" & FindEndRow())
   ' ActiveChart.SetSourceData Source:=Sheets(2).range("B36" & ":B" & FindEndRow())


    ActiveChart.SeriesCollection(1).Name = "=""Laser"""
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "=""R-Eye"""
    ActiveChart.SeriesCollection(2).Values = Sheets(2).range("t" & DataStart & ":t" & FindEndRow())
    'ActiveChart.SeriesCollection(2).Values = Sheets(2).range("t36" & ":t" & FindEndRow())
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "=""L-Eye"""
    ActiveChart.SeriesCollection(3).Values = Sheets(2).range("q" & DataStart & ":Q" & FindEndRow())
    'ActiveChart.SeriesCollection(3).Values = Sheets(2).range("q36" & ":Q" & FindEndRow())
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).AxisGroup = 2
    
    With ActiveChart.Parent
         .Height = 325  ' resize
         .Width = 1000  ' resize
         .Top = 10      ' reposition
         .Left = 10     ' reposition
     End With
 
With ActiveChart
     'chart name Top
    .HasTitle = True
    .ChartTitle.Characters.Text = "Laser Vs Eye for file:  " & Sheets(2).range("b1").Value
     ActiveChart.ChartTitle.Font.Size = 20
     'X axis name
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Data Pairs"
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Size = 15
     'y-axis name (Left hand side)
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Reflectivity"
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 15
      'Secondary Y axis (Right hand side)
    .Axes(xlValue, xlSecondary).HasTitle = True
    .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Values"
    .Axes(xlValue, xlSecondary).AxisTitle.Characters.Font.Size = 15
    
End With


End Sub

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


Function FindEndRow() As Integer
' Find end of data in row


FindEndRow = Sheets(2).range("b65536").End(xlUp).Row


End Function


Sub d_form()
userform1.Show


End Sub
'==============================================================

Function FindDataStart() As Integer

'Find start of data in row.  Data starts after "Ave"


Dim rFound As range
    On Error Resume Next          'cells(column, row)


    With Sheets(2)


        Set rFound = .range("B:B").find(What:="ave", After:=.range("b1"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)


    On Error GoTo 0


        If Not rFound Is Nothing Then Application.Goto rFound, True


    End With
        
     'move to first row for data after "Ave"
    ActiveCell.Offset(1, 0).Select
    FindDataStart = ActiveCell.Row
   
    
    End Function