Plotting charts; Object Variable or with Block not set... but it worked 2 min ago...

SnyperBunny

New Member
Joined
Jan 21, 2013
Messages
23
Hi, I've written a vba code to graph a large number of graphs for me with two data series on each graph. To test my theory on it working I broke it down into parts and ran each seperately as I went so I wouldnt have to sift through the whole thing to find an issue when I was finally finished... well I'm finished and it has a random error that for the life of me I cannot spot... If someone could please help me find what is causing this error I would be SO grateful! :)

Code:
Sub chart()
    Dim n As Integer 'worksheet index number that the chart goes on
    Dim date_col As String
    Dim m_max As String
    Dim m_min As String
    Dim m_avg As String
    Dim a_max As String
    Dim a_min As String
    Dim a_avg As String
    Dim k As Integer
    
    
    'n = 3
    date_col = "A"
    m_max = "C"
    m_min = "G"
    m_avg = "E"
    a_max = "B"
    a_min = "F"
    a_avg = "D"
    
    
For k = 1 To 10 Step 1
    n = k + 3
    'Maximum Temperature
    ThisWorkbook.Worksheets(n).Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLines
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "Actual Max"
    ActiveChart.SeriesCollection(1).XValues = Worksheets(2).Range(date_col & "3:" & date_col & "122")
    ActiveChart.SeriesCollection(1).Values = Worksheets(2).Range(a_max & "3:" & a_max & "122")

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "Model Max"
    ActiveChart.SeriesCollection(2).XValues = Worksheets(2).Range(date_col & "3:" & date_col & "122")
    ActiveChart.SeriesCollection(2).Values = Worksheets(2).Range(m_max & "3:" & m_max & "122")
    
[COLOR=#008000]I stuck a end sub here for the sake of trying to isolate the part throwing the error and it still came when I ran the top part. :( This is especially confusing since I ran it just a few minutes ago as I was testing the pieces I wrote as I was writing it and ran just fine with no errors. I have absolutely no idea what is going wrong.... [/COLOR]

    'Average Temperature
    Worksheets(n).Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLines
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "Actual Average"
    ActiveChart.SeriesCollection(1).XValues = Worksheets(2).Range(date_col & "3:" & date_col & "122")
    ActiveChart.SeriesCollection(1).Values = Worksheets(2).Range(a_avg & "3:" & a_avg & "122")
    
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "Model Average"
    ActiveChart.SeriesCollection(2).XValues = Worksheets(2).Range(date_col & "3:" & date_col & "122")
    ActiveChart.SeriesCollection(2).Values = Worksheets(2).Range(m_avg & "3:" & m_avg & "122")
    
    'Minimum Temperature
    Worksheets(n).Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLines
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "Actual Min"
    ActiveChart.SeriesCollection(1).XValues = Worksheets(2).Range(date_col & "3:" & date_col & "122")
    ActiveChart.SeriesCollection(1).Values = Worksheets(2).Range(a_min & "3:" & a_min & "122")
    
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "Model Min"
    ActiveChart.SeriesCollection(2).XValues = Worksheets(2).Range(date_col & "3:" & date_col & "122")
    ActiveChart.SeriesCollection(2).Values = Worksheets(2).Range(m_min & "3:" & m_min & "122")
    
    m_max = Chr(Asc(m_max) + 7)
    m_min = Chr(Asc(m_min) + 7)
    m_avg = Chr(Asc(m_avg) + 7)
    a_max = Chr(Asc(a_max) + 7)
    a_min = Chr(Asc(a_min) + 7)
    a_avg = Chr(Asc(a_avg) + 7)
    date_col = Chr(Asc(date_col) + 7)
    
    'n = n + 1
    Next k
'loop
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Argh... Can't edit original post anymore and I just realized that I didn't clearly state what the error I'm getting actually is... whoops :oops:

It is giving me "Error 91: Object Variable or With Block not set". The thing is that I isolated and ran just the top part of my code (above where I put the green comment in) and it is still giving me this error... except that I had run the same section previously and it ran perfectly. I did make some changes to it since then, however even when reverting these changes back to what they were before it is still throwing the same error.

The worst part of this is that its not highlighting a line as it usually does when throwing errors... Its just shouting at me and making me sad...
 
Upvote 0
*bump* I really need to figure this out for work... any help is greatly appreciated guys! I'll even sweeten the deal with offer of a virtual cookie...? (they're digi-licious~ you know you want one~) :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top