Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Macro to create charts
I recently had this question answered very comprehensively. However, I've
been told that I need to show my data on a chart in % form. Can anyone help modify the following macro to automatically generate charts where the source data is on a row by row basis with four columns showing data (don't need to show targets on this one). I've tried to modify it myself, but can't seem to get it working. The columns are all next to each other - no gaps. To clarigy, I need Name, %Score1, %Score2, %Score3 & %Score 4. That's it. Regards, JDB Jon Peltier In: microsoft.public.excel.charting Select your range (including header row) and run this macro: Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) < "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Macro to create charts
The easiest thing to do is to use a separate range that contains formulas
that convert the raw values to percentages, then apply this VBA procedure to the calculated range. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... I recently had this question answered very comprehensively. However, I've been told that I need to show my data on a chart in % form. Can anyone help modify the following macro to automatically generate charts where the source data is on a row by row basis with four columns showing data (don't need to show targets on this one). I've tried to modify it myself, but can't seem to get it working. The columns are all next to each other - no gaps. To clarigy, I need Name, %Score1, %Score2, %Score3 & %Score 4. That's it. Regards, JDB Jon Peltier In: microsoft.public.excel.charting Select your range (including header row) and run this macro: Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) < "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JDB" wrote in message ... Hi, I have a sheet of data that relates to individual performance scores. Each person has their data on one row which goes - Name, Team, Score 1, Target, Score 2, Target, Score 3, Target, Score 4, Target. I need to produce an individual graph for each person containing only the 4 score results. As there are over 120 individuals, is there a way, without manually creating each one of using a macro or something to make chart creation easier? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a macro to create charts automatically? | Charts and Charting in Excel | |||
please help... how to create org charts via VBA... | Charts and Charting in Excel | |||
Macro to create charts? | Charts and Charting in Excel | |||
how do i create 4 pie charts | Charts and Charting in Excel | |||
Vb macro stops when I try to create more than 89 charts | Charts and Charting in Excel |