Object variable or With block variable not set Run-time error

[hill0093]

I decided to learn VBA for Excel 2003 to relieve me of some drudgery.
These are the first few lines of a loop in a 2003 Excel VBA program I wrote:

For iChtRow=1 to nOfChtRows
For iChtCol=1 to nOfChtCols
Windows("elip4xls.txt").Activate
Worksheets("elip4xls").Activate
iCht=(iChtRow-1)*nOfChtCols+iChtCol
wsCol=iCht*2-1
If Not IsEmpty(Sheets("elip4xls").Cells(28, wsCol)) Then
Dim elipRang as Range
elipRang = Activesheet.Range(Cells(28, wsCol),Cells(92, (wsCol + 1)))
'Object variable or With block variable not set Run-time error '91' for previous line
'but the following line will work
elipRang = Worksheets("elip4xls.txt").Range(Sheets("elip4xls").Cells(28, wsCol), Sheets("elip4xls").Cells(92, (wsCol + 1)))
Charts.Add

What did I do wrong?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

To assign a Range Object you need to use the keyword "Set"

Set elipRang = Activesheet.Range(Cells(28, wsCol),Cells(92, (wsCol + 1)))

I haven't studied the rest of your code to see if there are any other problems,
but you'll need to use Set to fix that one line.

 

[hill0093]

Thanks, Looks like you do things right.
I'll try it.

 

[hill0093]

The "Set" worked, but I put the Range right into the statement
For iChtRow=1 to nOfChtRows
For iChtCol=1 to nOfChtCols
Windows("elip4xls.txt").Activate
Worksheets("elip4xls").Activate
iCht=(iChtRow-1)*nOfChtCols+iChtCol
wsCol=iCht*2-1
If Not IsEmpty(Sheets("elip4xls").Cells(28, wsCol)) Then
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

'NEXT IS THE FIRST AND LONG "SERIES" CONTINUOUS LINE
'The "Set" worked, but I put the Range right in the next statement
ActiveChart.SetSourceData Source:=Sheets("elip4xls") _
.Range(Sheets("elip4xls").Cells(28, wsCol), _
Sheets("elip4xls").Cells(92, (wsCol + 1))),PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="elip4xls"
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False

'NEXT IS ONE OF THE EIGHT SHORTER "SERIES"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "=elip4xls!R7C1:R10C1"
ActiveChart.SeriesCollection(2).Values = "=elip4xls!R7C2:R10C2"
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerStyle = xlNone
End With

'NEXT IS ANOTHER OF THE EIGHT SHORTER "SERIES"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = "=elip4xls!R12C1:R14C1"
ActiveChart.SeriesCollection(3).Values = "=elip4xls!R12C2:R14C2"
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerStyle = xlNone
End With

I want to know how to do the eight series by a loop using different rows as well as
how to make them correspond to the correct worksheet columns of the outer two loops.
The rows and lengths correspond to this small table:
row: 1,4,7,12,16,19,22,25
nPts:2,2,4, 3, 2, 2, 2, 2
And I am considering using a case statement similar to the following:
Select Case wsRow
Case 1: pntCnt = 2
Case 4: pntCnt = 2
Case 7: pntCnt = 4
Case 12: pntCnt = 3
Case 16: pntCnt = 2
Case 19: pntCnt = 2
Case 22: pntCnt = 2
Case 25: pntCnt = 2
End Select
But my real problem is how do I "Range-ize" the statements like this one:
ActiveChart.SeriesCollection(3).XValues = "=elip4xls!R12C1:R14C1"

 

[hill0093]

Trial showed that
ActiveChart.SeriesCollection(4).XValues = Sheets("elip4xls").Range(Sheets("elip4xls").Cells(7, 1), Sheets("elip4xls").Cells(10, 1))
ActiveChart.SeriesCollection(4).Values = Sheets("elip4xls").Range(Sheets("elip4xls").Cells(7, 2), Sheets("elip4xls").Cells(10, 2))
works for
ActiveChart.SeriesCollection(4).XValues = "=elip4xls!R7C1:R10C1"
ActiveChart.SeriesCollection(4).Values = "=elip4xls!R7C2:R10C2"
so I solved my main problem.
Sorry, I don.t know how to make this post look nice.

 

[Jerry Sullivan]

Hi,

It's probably best if we break this down into a few steps so we can get it each part to work before moving on to the next.

1. Are you trying to make one chart with many series or many charts with many series?
2. It looks like your iCht values resulting from:
iCht=(iChtRow-1)*nOfChtCols+iChtCol
...will just be 1,2,3,.... is that correct?

3. It looks like your wsCol values resulting from:
wsCol=iCht*2-1
...will just be 1,3,5,7,.... is that correct?

If those last two items are correct, we could probably simplify things by eliminating the nexted loop.