By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

Nasty "With block variable not set" Error

P: 5
Here's the deal. I'm trying to write an excel macro in VBA that will take a bunch of data and create a nice bubble chart out of it. First, it checks if the row I'm adding to the chart is the first row. If it is, we need to create a new chart. If it isn't, then we meerly need to add on to the existing chart. However, I never get to that addition step because I get Run-time error '91' when trying to add the first row...here's the code.

Sub CreateGraph()
Dim chtChart As Chart
Set chtChart = Charts.Add
With chtChart
.Name = currentChartTitle
.SetSourceData Source:=Sheets("Master Data Sheet").Range("B" & ActiveCell.row & ":D" & ActiveCell.row) _
, PlotBy:=xlColumns
.ChartType = xlBubble
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
.HasLegend = False
End With
With chtChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
.MinimumScale = 3
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
With chtChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
End Sub

The String currentChartTitle is a global variable and is already set and works, so that's not the problem. I got a different error when I had the .ChartType = xlBubble before the .SetSourceData, and people said I had to switch it around for it to work. Now I get the Run-time 91 error in that .SetSourceData line. Can anyone help me out?
Jul 9 '08 #1
Share this Question
Share on Google+
1 Reply


P: 5
Nevermind. I think the problem was I didn't have an ActiveCell because the chart sheet was activated, so I created another global variable that set the ActiveCell.row before creating the chart sheet, and that fixed it. Thanks anywho. No worries, I'm working on some new and exciting (and huge) projects, so I'll be in and out of these forums. Hope to see everyone around!
Jul 9 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.