Macro for Graph

[rajltd]

For the following table, I have written the macro to obtain the graph
But the problem is the no. of column and rows can change... So I don
want to hard code particularly the first part of macro... Please help.


Keyword Date Total Of Value XYZ TUV RST
ABC 27/10/2003 7 7 11 100
ABC 27/11/2003 7 3 5 15
ABC 27/12/2003 7 10 200 25


Sub Graph()

Charts.Add

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn
TypeName:="Logarithmic"
ActiveChart.SetSourceDat
Source:=Sheets("Keyword_Analysis").Range("B2:F4"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Name = "=Keyword_Analysis!R1C4"
ActiveChart.SeriesCollection(2).Name = "=Keyword_Analysis!R1C5"
ActiveChart.SeriesCollection(3).Name = "=Keyword_Analysis!R1C6"

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1"

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ABC"
.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True




.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Position"

End With

ActiveChart.PlotArea.Select
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue
LegendKey:=False

ActiveChart.Legend.Select

With Selection.Interior
.ColorIndex = 34
End With

ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
End With

ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
End With

ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True

With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With

ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
End With

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True

With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With

With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = 45
End With

ActiveChart.Legend.Select

Selection.Shadow = True

With Selection.Interior
.ColorIndex = 34
End With

ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
End With

With Selection.Interior
.ColorIndex = 34
.PatternColorIndex = 1
.Pattern = xlSolid
End With

ActiveChart.ChartArea.Select
Selection.Shadow = True


End Sub


Thanks

Ra

 

[rajltd]

can anyone reply to this mai

 

[whisperer]

Hope that the following is what you were looking for, changes to your
code are in Red


Code:
--------------------

Sub Graph()
Dim EndCell As Range
Dim iRow As Integer, iCol As Integer, iSeries As Integer

iRow = Cells(Rows.Count, 1).End(xlUp).Row
iCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set EndCell = Cells(iRow, iCol)
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Logarithmic"
ActiveChart.SetSourceData Source:=Sheets("Keyword_Analysis").Range("B2", EndCell), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
For iSeries = 1 To iCol - 3
ActiveChart.SeriesCollection(iSeries).Name = "=Keyword_Analysis!R1C" & iSeries + 3
Next iSeries
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ABC"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Position"
End With
ActiveChart.PlotArea.Select
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
ActiveChart.Legend.Select
With Selection.Interior
.ColorIndex = 34
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 20
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = 45
End With
ActiveChart.Legend.Select
Selection.Shadow = True
With Selection.Interior
.ColorIndex = 34
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
End With
With Selection.Interior
.ColorIndex = 34
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.ChartArea.Select
Selection.Shadow = True
End Sub

 

[whisperer]

Sorry it is not quite right yet as it does not correctly display th
date element but it could serve to put you on the right lines.:mad