The below requires that a sheet "Chart" exists, and
several range names have been defined.

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
Dim i As Integer
Dim k As Integer
Dim numdrugs As Integer
Dim drugrow As Integer
Dim drug1col As Integer
Dim drug(100) As String
Dim Rating(100) As Integer
Dim chartdata As Range
Sheets("data").Select
Sheets("chart").Delete
'
'assumes drug names exist in a contiguous row with left
hand name
'in a cell named "drug1"
'
Range("chartdata").Clear
Range("drug1").Select
numdrugs = Selection.End(xlToRight).Column - _
Range("drug1").Column
drug1col = Range("drug1").Column
drugrow = Range("drug1").Row
'
'store data for drugs with rating over zero
'
k = 1
For i = drug1col To drug1col + numdrugs
If Cells(drugrow + 1, i).Value = 0 Then GoTo nexti
drug(k) = Cells(drugrow, i).Text
Rating(k) = Cells(drugrow + 1, i).Value
If i = drug1col + numdrugs Then GoTo nexti
k = k + 1
nexti:
Next i
'
'place data for drugs with rating over zero on chartdata
sheet
'in columns 1 (name) and 2 (rating) beginning in row 1
'
Sheets("Chartdata").Select
For i = 1 To k
Cells(i, 1).Value = drug(i)
Cells(i, 2).Value = Rating(i)
Next i
Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="chartdata",
RefersToR1C1:= _
"=chartdata!R1C1:R4C2"
Range("chartdata").Sort Key1:=Range("B1"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Charts.Add
ActiveChart.ChartType = xlColumnClustered
With ActiveChart
.SetSourceData Source:=Sheets("chartdata").Range
("chartdata"), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Drug Ratings"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory,
xlPrimary).AxisTitle.Characters.Text = "Drugs"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue,
xlPrimary).AxisTitle.Characters.Text = "Ratings"
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveSheet.Name = "Chart"
End Sub

-----Original Message-----
Hi,
I'm really very new to VBA, so I don't even know how to
approach my
problem.

My spreadsheet is meant for various kinds of medication.
Each medicin
ist tested against various illnisses and then receives a
rating in 12
categories. The ratings are summed up in a final cell via
the =SUM
function. But not each medicin qualifies for a rating and
sometimes a
medication won't receive any rating at all (the SUM cell
stays blank.)

What I want is a macro that makes a clustered column
chart from that
data. The chart should contain every medicin whose SUM
cell is not
empty. The Y-Axis (the values) is to be made up from the
SUM cells. The
X-Axis (description) should contain the corresponding
medication names.
Furthermore I want the columns to be sorted from highest
rating to
lowest.

My main problem is that I have no idea how to do
this :) . Any help is
greatly appreciated.


---
Message posted from http://www.ExcelForum.com/

.