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

My spreadsheet is meant for various kinds of medication. Each medici
ist tested against various illnisses and then receives a rating in 1
categories. The ratings are summed up in a final cell via the =SU
function. But not each medicin qualifies for a rating and sometimes
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 tha
data. The chart should contain every medicin whose SUM cell is no
empty. The Y-Axis (the values) is to be made up from the SUM cells. Th
X-Axis (description) should contain the corresponding medication names
Furthermore I want the columns to be sorted from highest rating t
lowest.

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

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

Turn on the macro recorder (ToolsMacroRecord).

Select one cell in your data table and then press F5 (go to) then clic
on Special, then click on Current Region. This will select your entir
data table without you having to specify exactly how large the tabl
is.

Sort the data table, descending by SUM column.

Now select only the entries with non-zero sums.

Click the chart wizard and set up the chart the way you want it.

Turn off the macro recorder.

Launch Visual Basic Editor (alt-F11).

Change some of the fixed values into variables. For example, you wil
want to vary how many rows of data are non-zero. So you will want
loop that does something like this (assuming the SUM column is colum
"F", and the data start in Row 2):

LastRow = ActiveSheet.cells.specialcells(xlLastCell).Row
For i = 2
If cells(i, "F") = "" or (isNumeric(cells(i, "F")) AND _
cells(i, "F")<=0) then goto LastRowFound
next i

LastRowFound:
If i<LastRow then LastRow = i-1

Good luck. This is the way that we all started

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

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/

.

I had an error previously - sorry about that.

John

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
'
' assumes raw data is on a sheet named "data" and a sheet
named
' "chart" exists
'
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").CurrentRegion.Name = "chartdata"
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-----
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/

.

.

Thank you very much. Code implemented, problem solved :

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