• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with excel chart

J

james_kay

New Member
I have to amend a chart using VBA to plot column A(program) against column C(Date), at the moment it is mapped as column A(program) against column B(startsec).


the piece of VBA code that charts can be seen here

[pre]
Code: 
Sub createprocesscharts()
Dim sets(2, 365) As Long
Dim limits(2, 365) As Long
Dim dates(365) As String
c = 1
r = 2
Sheets("Sheet1").Select
dt = Cells(r, 5)
sets(1, c) = r
limits(1, c) = Cells(r, 2)
While (Cells(r, 5) <> "")
If Cells(r, 5) <> dt Then
sets(2, c) = r - 1
limits(2, c) = Cells(r - 1, 2)
dates(c) = Format(Cells(r - 1, 5), "ddd, d mmm yyyy")
dt = Cells(r, 5)
c = c + 1
sets(1, c) = r
limits(1, c) = Cells(r, 2)
End If
r = r + 1
Wend
sets(2, c) = r - 1
limits(2, c) = Cells(r - 1, 2)
dates(c) = Format(Cells(r - 1, 5), "ddd, d mmm yyyy")

For i = 1 To c
Debug.Print sets(1, i) & " " & sets(2, i) & " " & limits(1, i) & " " & limits(2, i) & " " & dates(i)
Call makechart(sets(1, i), sets(2, i), dates(i), limits(1, i), limits(2, i))
Next

End Sub

Sub makechart(r1 As Long, r2 As Long, dt As String, l1 As Long, l2 As Long)
Sheets("Sheet1").Select
rstart = "$A$" & r1
rend = "$C$" & r2
rg = "'Sheet1'!" & rstart & ":" & rend

ActiveSheet.Shapes.AddChart.Select

With ActiveChart
.SetSourceData Source:=Range(rg)
.ChartType = xlBarStacked
.Legend.Delete
.Axes(xlValue).MinimumScale = l1
.Axes(xlValue).MaximumScale = l2
.SeriesCollection(1).Interior.ColorIndex = xlNone
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickLabels.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.Size = 8
.Location Where:=xlLocationAsNewSheet, Name:=dt
End With
End Sub
[/pre]

in essense , I want the `

.Axes(xlValue).MinimumScale = l1

.Axes(xlValue).MaximumScale = l2

` to take values from the E column and not C column.


I would be grateful for any help/suggestions for this issue.


I have uploaded the excel chart in here


https://www.dropbox.com/s/fo8ipmjpgem46go/create_chart.xls
 
Hi James ,


Can you check the file here ?


http://speedy.sh/zGRxp/create-chart-1.xls


I have made only one change , since I have not gone through the entire procedure ; what I find is that along with this change , if you hide column B of your data , the charts appear to be OK. Can you see this ?


Narayan
 
Hi Narayan ,

thanks for the feedback. sorry I made a mistake in my post .

what i wanted to plot is column A(program) against column F(STARTTIME). I tried to hide columns like you did , but it isn't working for me.

Thanks

James.
 
Hi Narayan,

is it possible to change the chart you uploaded to a Stacked Bar or column with an additional column "durationsec".could you change the code please ? ]

example chart of what I mean is here.


http://dl.dropbox.com/u/36755932/example%20chart.xls


thanks

james
 
You must log in or register to reply here.
Back
Top