hey
i have a table of data see image below of names of companies and then a value either +ve or -ve
I want to plot only the positive values in a pie chart. I don't want to reorder the data (unless at the end of the macro it will reorder it back again without changing the chart).
Screen Shot 2013-05-24 at 02.55.34.jpg
i have tried and tried and tried for hours and hours and hours to get this right. i am new to VB but keen to learn.
i hit on the idea of using a loop through the cells, and using the UNION function it will add to the range if it finds a positive value. Unfortunately I have had problems:
1. the top value isn't necessarily positive.
2. i want it to skip the pie chart (and complete the rest of my macro which does other stuff) if there are no positive values
the below code is fairly concise (apart from right at the end when formatting the charts which was recorded) so please please can you help and not be deterred by the length!
i would be very grateful, youve no idea how many hours ive spent trying.
thank you
note mkt is defined previously as the upper left cell in the table
Sub FinalPivot()
'
'
Charts
Dim aggcell As Range
Set aggcell = mkt.Offset(1, 1)
Dim aggend As Range
Set aggend = aggcell.End(xlDown).Offset(-1, 0)
Dim chartseries As Range
For i = aggcell.Row To aggend.Offset(1, 0).Row
If Cells(i, aggcell.Column).Value >= 0 Then
Set chartseries = Range(Cells(i, aggcell.Column - 1), Cells(i, aggcell.Column))
GoTo Quit
Else
End If
Next i
For i = aggcell.Row To aggend.Row
If Cells(i, aggcell.Column).Value >= 0 Then Set chartseries = Union(chartseries, Range(Cells(i, aggcell - 1), Cells(i, aggcell)))
Next i
Quit:
If chartseries Is Nothing Then GoTo Jump Else
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
With .chart
.ChartType = xlPie
.SetSourceData Source:=chartseries
.Parent.Name = "Pie"
End With
End With
ActiveSheet.ChartObjects("Pie").Activate
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 42
ActiveChart.ClearToMatchStyle
ActiveChart.Legend.Delete
ActiveChart.SetElement (130)
ActiveChart.Name = "Pie"
ActiveSheet.ChartObjects("Pie").Activate
ActiveSheet.Shapes("Pie").IncrementLeft -427
ActiveSheet.Shapes("Pie").IncrementTop 101
ActiveWindow.SmallScroll Down:=11
ActiveSheet.ChartObjects("Pie").Activate
ActiveSheet.Shapes("Pie").ScaleWidth 1.5069444444, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Pie").ScaleHeight 1.5069444444, msoFalse, _
msoScaleFromTopLeft
ActiveWindow.SmallScroll Down:=-17
ActiveSheet.ChartObjects("Pie").Activate
ActiveSheet.Shapes("Pie").IncrementTop -30
Range("Table1[#All]").Select
Jump:
Bookmarks