What are typical values for krows and kcols?

At first glance I notice that in the Select Case PlotBy, under Case
xlColumns, you have values defined by

"=" & ActiveSheet.Name & "!R25C" & StartCol & ":R25C" & StartCol +
EndCol - 2

which means it is a range one row high by several columns wide (the same
type of values definition appears in several places in the worksheet name
select case). This is the definition of "by row", isn't it?

You should do the SetSourceData before defining SeriesCollection(1).Name.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Peace" wrote in message
ups.com...
Hello Jon, Appreciate your response. I wondered if anyone had had a
similar issue for which a solution already exists. I did not post the
code as it is very lengthy, and in different modules. I am hopefully
reproducing the relevant portion of it. I would be happy to share the
workbook if that would help. Thanks.

The code is designed to chart data by column, or by row, depending on
the position of the active cell in the table underlying the chart. If
the activecell is other than row 15, and is in column 1, then the row
provides data points for the chart. If the active cell is in row 15
and other than column 1, then that column provides the data points.
If the active cell is elsewhere, it is forced to 'come' to row 15,
column 1 till the user makes a selection.

Sub UpdateChart()

On Error Resume Next
Set TheChartObj = ActiveSheet.ChartObjects(1)
Set TheChart = TheChartObj.Chart
UserRow = ActiveCell.Row
UserCol = ActiveCell.Column

krows = _
Application.WorksheetFunction.CountA(ActiveSheet. _
Range("A:A").SpecialCells(xlCellTypeVisible))
kcols = _
Application.WorksheetFunction.CountA(ActiveSheet. _
Rows("15:15").SpecialCells(xlCellTypeVisible))

'================================================= =====================
'clear shading of rows
Range(Cells(16, 2), Cells(100, 100)).Interior.ColorIndex = xlNone
'================================================= =====================

'code to shift from row to columns
If UserCol 1 And UserCol < kcols + 1 And UserRow = 15 Then
'for vertical
Set SrcRange = ActiveSheet.Range(Cells(15, UserCol), Cells(15 +
krows - 1, UserCol))
PlotBy = xlColumns
ElseIf UserRow 15 And UserRow < (15 + krows) And UserCol = 1 Then
'for horizontal
Set SrcRange = ActiveSheet.Range(Cells(UserRow, StartCol),
Cells(UserRow, EndCol))
Set SrcRange1 = ActiveSheet.Range(Cells(25, StartCol), Cells(25,
StartCol + EndCol))
PlotBy = xlRows
Else
Range("A15").Select
PlotBy = 0
End If
'================================================= =====
Select Case PlotBy

Case 1 ' by xlColumns
SrcRange.Interior.ColorIndex = 7
TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
UserRow & "C1"
TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy
TheChart.SeriesCollection.NewSeries
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

'set label for each series
Set xlblrng = ActiveSheet.Range(Cells(15, StartCol), Cells(15,
StartCol + EndCol - 2))
For Each srs In TheChart.SeriesCollection
srs.XValues = xlblrng
Next
TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B77") &
vbCr & ActiveSheet.Range("B78")
'----------------------------------------
Case 2 ' by xlrows
SrcRange.Interior.ColorIndex = 4
TheChart.SetSourceData Source:=SrcRange, PlotBy:=PlotBy

'set label for each series
Set xlblrng = ActiveSheet.Range(Cells(16, 1), Cells(16 + krows, 1))
For Each srs In TheChart.SeriesCollection
On Error Resume Next
srs.XValues = xlblrng
Next
TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
vbCr & ActiveSheet.Range("B76")

End Select
'================================================= =====
Select Case ActiveSheet.Name
Case Is = "Region_Year_o_Year"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Cross-Section for Year " &
Format(Cells(15, UserCol).Value, "yyyy")
End If

TheChart.HasTitle = True
TheChart.Axes(xlCategory).AxisTitle.Text = ActiveSheet.Range("B75") &
vbCr & ActiveSheet.Range("B76")

Case Is = "State_Year_o_Year"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 8)).Interior.ColorIndex = 15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlYears
kMinorUnitScale = xlYears
ChartTitleText = "HPA Cross-Section for Year " _
& Format(Cells(15, UserCol).Value, "yyyy")
End If

Case Is = "Region_Qrtr_o_Qrtr"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Quarter ending " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")
End If

Case Is = "State_Qrtr_o_Qrtr"

TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 29)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 3
kMinorUnit = 3
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Quarter ending " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")

End If

Case Is = "Region_Month_o_Month"
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R25C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R25C"
& StartCol & ":R25C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(25, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Month " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")
End If

Case Is = "State_Month_o_Month"
TheChart.SeriesCollection(2).Name = "=" & ActiveSheet.Name & "!R67C1"
TheChart.SeriesCollection(2).Values = "=" & ActiveSheet.Name & "!R67C"
& StartCol & ":R67C" & StartCol + EndCol - 2
TheChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

With TheChart
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"United States"
End With

ActiveSheet.Range(Cells(15, 1), Cells(15, 85)).Interior.ColorIndex =
15
ActiveSheet.Range(Cells(15, 1), Cells(68, 1)).Interior.ColorIndex = 15
If PlotBy = 1 Then
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' horizontal = timeseries
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Time-Series for " & Cells(UserRow,
1).Value
Else
kMajorUnit = 1
kMinorUnit = 1
TkLblSpc = 1 ' vertical = cross section
kChartType = 51
kMajorUnitScale = xlMonths
kMinorUnitScale = xlMonths
ChartTitleText = "HPA Cross-Section for Month " _
& Format(Cells(15, UserCol).Value, "mmm-yyyy")
End If
End Select

'==========================================
Select Case PlotBy

Case 1 ' by xlColumns, timeseries, horizontal
With ActiveSheet.ChartObjects
' .Top = Range(Cells(2, StartCol), Cells(2, StartCol)).Top
.Left = Range(Cells(2, StartCol), Cells(2, StartCol)).Left
End With
ActiveWindow.ScrollColumn = StartCol

Case 2 ' by xlrows = vertical
With ActiveSheet.ChartObjects
' .Top = Range(Cells(2, ActiveCell.Column), Cells(2,
ActiveCell.Column)).Top
.Left = Range(Cells(15, ActiveCell.Column), Cells(15,
ActiveCell.Column)).Left
End With
ActiveWindow.ScrollColumn = ActiveCell.Column
End Select

With TheChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = kMajorUnit
.MajorUnitScale = kMajorUnitScale
.MinorUnit = kMinorUnit
.MinorUnitScale = kMinorUnitScale
.Crosses = xlCustom
.AxisBetweenCategories = False
.ReversePlotOrder = False

.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
TheChart.ChartTitle.Text = ChartTitleText
TheChart.Activate
ActiveChart.Axes(xlCategory).Select
'With Selection.TickLabels
'.Alignment = xlCenter
'.Offset = 100
'.ReadingOrder = xlContext
'.Orientation = 90
'End With

'TheChartObj.Chart.ChartType = 51
TheChart.SeriesCollection(1).Name = "=" & ActiveSheet.Name & "!R" &
UserRow & "C1"
TheChart.Refresh
TheChart.Axes(xlCategory).TickLabelPosition = xlLow
TheChartObj.Visible = True
Application.ScreenUpdating = True
End Sub


On Mar 21, 4:01 pm, "Jon Peltier"
wrote:
Hard to tell if you keep the code secret.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______

"Peace" wrote in message

oups.com...



I use VBA to create charts in Excel 2003, but find that sometimes the
Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not
intended), even if I have specified 'Series in Rows'. This happens
intermittently, and I am not sure what I am doing wrong. I do save
the workbook as Microsoft Excel 97 so that a user with Excel 2000 or
Excel 2003 can use the workbook.

Thank you for any suggestions.- Hide quoted text -

- Show quoted text -