PDA

View Full Version : chart with vba



skaswani
08-26-2007, 07:29 AM
actually, I have a question regarding Charts with Vba



what I want to do

1) Loop all sheets (almost 100 sheets)

2) in Loop I want to create CHARTS

3) problem is that all sheets have different number of rows!

I.e, some sheets have 100 records, some have 500 & some have more!

but the data is of 2 years

so, is this possible to make such code in VBA

I have made a code, but,

actually it doesn't allow me to RE-Create Charts because it allocate a Name to that chart which is static,

2nd, the same it does with Sheet name (Data Source) it actually use the static name as I mention above



please kindly help me out


I am pasting the code here,

my Charts are Base on DATE & Closing RATES




regards,





Sub MakeChart()

Dim Current As Worksheet


For Each Current In Worksheets

Sheets(Current.Index).Select



Charts.Add

ActiveChart.ChartType = xlLineMarkers

ActiveChart.SetSourceData Source:=Sheets("AL-Habib").Range("A3:B360"), _
PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="AL-Habib"

ActiveChart.HasDataTable = False

ActiveWindow.Visible = False

ActiveWindow.WindowState = xlNormal

ActiveWindow.WindowState = xlMaximized

Range("F6").Select

ActiveSheet.ChartObjects("Chart 3").Activate

ActiveChart.Paste

ActiveChart.Legend.Select

Selection.Delete

ActiveChart.Axes(xlCategory).Select

Selection.TickLabels.NumberFormat = "[$-409]mmm-yy;@"

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft 156#
ActiveSheet.Shapes("Chart 3").IncrementTop -38.25
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "[$-409]mmm-yy;@"

Next

End Sub

mdmackillop
08-26-2007, 07:46 AM
Hi Skaswani
Welcome to VBAX
Can you post a small sample (two or three sheets) . Use Manage Attachments in the Go Advanced section.
Regards
MD

skaswani
08-26-2007, 08:10 AM
hello, i dont know where i can find that option:(

please kindly give me the direct link or can i email u??

regards,

skaswani
08-26-2007, 08:13 AM
here it is:)

thanks,

geekgirlau
08-26-2007, 07:00 PM
Sub MakeChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart


For Each wks In ActiveWorkbook.Worksheets
Set rng = wks.Range("A2:B" & wks.UsedRange.Rows.Count)
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)

With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy:=xlColumns

On Error Resume Next
.HasDataTable = False
.HasLegend = False

.Axes(xlCategory).TickLabels.NumberFormat = "[$-409]mmm-yy;@"

With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 300
End With
End With
Next wks
End Sub

skaswani
08-27-2007, 01:09 PM
hey GeekGirl

how are you
thankyou so much for this code!

i have modified it as per my need!

the only one problem which i am facing in the end that


- i want to loop till CELL/Column "A" is empty

the loop code which you gave is not working on my sheet, bec my sheet have some IF & other Forumla's even if the Column A is empty
so, is this possible to Loop till Cell A is NOT EMPTY

btw, Cell A have Dates

here is my Code!




Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart

Sub mkChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart
For Each wks In ActiveWorkbook.Worksheets

Set rng = wks.Range("A3:B360")


Set cht = Charts.Add

Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)




With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy:=xlColumns




On Error Resume Next
.HasDataTable = False
.HasLegend = False

.Axes(xlCategory).TickLabels.NumberFormat = "[$-409]mmm-yy;@"


With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 350
End With
End With

Call change_unit
Next wks


MsgBox "Done"
UserForm1.Hide
End Sub


Sub change_unit()
For Each wks In ActiveWorkbook.Worksheets


ActiveChart.PlotArea.Select
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With


ActiveChart.PlotArea.Select
Selection.ClearFormats

Next wks


End Sub


Edited 28-Aug-07 by geekgirlau. Reason: insert VBA tags

geekgirlau
08-27-2007, 05:17 PM
Okay, try this:


Sub mkChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart


For Each wks In ActiveWorkbook.Worksheets
Set rng = wks.Range(wks.Range("A1"), wks.Range("A" & Cells.Rows.Count).End(xlUp))
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)

With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=rng, PlotBy:=xlColumns

On Error Resume Next
.HasDataTable = False
.HasLegend = False

With .Axes(xlCategory)
.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With

.PlotArea.ClearFormats

With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 350
End With
End With
Next wks

MsgBox "Done"
End Sub


A couple of things to note:

Please use the vba tags when posting code - I've edited your post above for you. When you paste the code into the thread, select the text then click on the "VBA" button.
I've removed your "change_unit" procedure and incorporated it into the "mkChart" procedure. Your method creates too many loops. The first procedure loops through every worksheet, and calls the second procedure that also loops through every worksheet. As the second procedure is just making further modifications to the same chart, it's much easier to just add those additional changes in the first pass.

skaswani
08-27-2007, 11:10 PM
Dear Mam,

Sorry, actuly i am new here in this Forum


thank you very much for helping me again & again,.

but i think you hav'nt check my WorkBook


let me explain you

1) for charts ,my data is starting from A 3 Cell, not with A 1
2) i have to use 2 columns, that are A & B

A have the Dates & B have Closing Rates

but when i run your code, it


start from Row A 1

it doesnot include Row B



sorry for trouble,

regards,

geekgirlau
08-27-2007, 11:34 PM
Change this line:


Set rng = wks.Range(wks.Range("A1"), wks.Range("A" & Cells.Rows.Count).End(xlUp))


to this:


Set rng = wks.Range(wks.Range("A3"), wks.Range("A" & Cells.Rows.Count).End(xlUp).Offset(0,1))

skaswani
08-27-2007, 11:42 PM
oops,

do u know what


the data source which it is using is the 1st Sheet :s

:s, any idea

geekgirlau
08-27-2007, 11:53 PM
I don't understand the question ... the data source for each chart is the range on the current sheet

skaswani
08-28-2007, 12:06 AM
yes, i also believe that is the right way, but when i check the data source of the CHART it take me to SHEET1

let me upload the file,


if you right click on the CHART for check the Source you will see this problem


regards,

geekgirlau
08-28-2007, 12:19 AM
Sub mkChart()
Dim wks As Worksheet
Dim rng As Range
Dim cht As Chart


For Each wks In ActiveWorkbook.Worksheets
wks.Range("A1").Select

Set rng = wks.Range(wks.Range("A3"), wks.Range("A" & wks.Cells.Rows.Count) _
.End(xlUp).Offset(0, 1))
Set cht = Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wks.Name)

With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=wks.Range(rng.Address), PlotBy:=xlColumns

On Error Resume Next
.HasDataTable = False
.HasLegend = False

With .Axes(xlCategory)
.TickLabels.NumberFormat = "[$-409]mmm-yy;@"
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 1
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With

.PlotArea.ClearFormats

With .Parent
.Top = wks.Range("G5").Top
.Left = wks.Range("G5").Left
.Height = 350
End With
End With
Next wks

MsgBox "Done"
End Sub

skaswani
08-28-2007, 12:29 AM
Thank You SO MUCH!

GOD BLESS YOU! ameen

lastly io want to ask, can you please recommend me any EBook for VBA!

i really want to learn it,


regards,!

geekgirlau
08-28-2007, 12:33 AM
I don't know of any personally, but you might want to search the site or post a new question to get some recommendations.

skaswani
08-28-2007, 12:51 AM
can i add a title to chart??

i try to add

like this

.ChartTitle.Characters.Text = wks.Name

any idea!

geekgirlau
08-28-2007, 05:54 PM
Almost ...


With cht
.ChartType = xlLineMarkers
.SetSourceData Source:=wks.Range(rng.Address), PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = wks.Name

skaswani
08-29-2007, 09:57 AM
thanks alot, i'll check it tomorow morning,


thanks again!