Please help with Excel Chart 1

[xitu]

Does anyone know how to write the VBA code to make Series?

I have this data:

6 3
2 1 Start
6 3
4 5
5 7
4 8
7 12 End
6 10
12 8
9 10 Start
6 4
7 6
4 7
3 8 End
6 5
2 9

How do I build a macro to make Series chart for each Start-End block?


Here's my code but it does not work:


Option Explicit

Sub test()
Dim CellAddr As String
Dim Cell_1 As String
Dim Cell_2 As String


While ActiveCell.Offset(0, -2).Value > 0 'activecell is col C
If ActiveCell.Offset(0, 0).Value = "START" Then
Cell_1 = ActiveCell.Offset(0, 1).Address
End If
If ActiveCell.Offset(0, 0).Value = "END" Then
Cell_2 = ActiveCell.Offset(0, 2).Address
End If

ActiveCell.Offset(1, 0).Select
Wend

CellAddr = "Cell_1: Cell_2"
Range(CellAddr).Select
Charts.Add
ActiveChart.ChartType = xlAreaStacked
ActiveChart.SetSourceData Source:=Sheets("Chart&quot.Range(CellAddr), PlotBy _:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

End Sub


I am learning Excel VBA. Please help.
Thanks,
XT

 

[xitu]

In SubPlotCharts()

If you uncomment PlotClockDiffP nSeries and run SubPlotCharts(), you will see a chart in a new sheet with two series. Then comment PlotClockDiffP nSeries, uncomment PlotClockDiffTemp and run SubPlotCharts(), you will see another chart in another new sheet with two series.

I tried your code but could not get the above result. Can you test your code in my file and send it to vu@sgtx.com?
BTW, will your code run well if I plot series for rngPercent vs. rngPressure (not rngPercent vs. rngClock)


'************* Uncomment one of these to make chart *************
PlotClockDiffP nSeries
' PlotClockDiffTemp nSeries
' PlotClockPercent nSeries
' PlotClockPressure nSeries
' PlotClockTemperature nSeries
' PlotCmbTotalPressure nSeries


Thanks,
XT

 

[SkipVought]

Your data sheet does not have data in columns 6-13. How does it get there?

The ONLY series that does not have rngClock as the category is rgCmbTotal/rngPressure.

All the others should do what you expect.

I AM NOT UNDERSTANDING YOUR PROBLEM.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xitu]

Skip,

In that worksheet, you have to click the button number 2 ("Set block Start-End&quot to get the data (calculation)

The columns I missed were 10 and 11.

Your module is "Module 3"
After running this module, I got Sheet 1 - Sheet 6 with only one Serie in each chart.

If you click on button number 3 (" 3. Plot Series & Add Title&quot, you will see one chart in a new sheet with two series. Please take a look at the updated file

http://viet.ws/sample.xls

, you will see what I mean.

Thanks,
XT

 

[xitu]

By the way, is there any way I can use the inputbox to control the comment/ uncomment problem by using CASE?

Ex:
SELECT_SERIES = InputBox("Enter 1 to plot PlotClockDiffP, 2 to plot PlotClockDiffTemp ..... ", "Enter your choice&quot

instead of having this
'************* Uncomment one of these to make chart *************
PlotClockDiffP nSeries
' PlotClockDiffTemp nSeries
' PlotClockPercent nSeries
' PlotClockPressure nSeries
' PlotClockTemperature nSeries
' PlotCmbTotalPressure nSeries

Thanks,
XT

 

[SkipVought]

This code makes one or more series per chart depending on the number of HP groups identified.

Sub PlotCharts()
    Set wsChart = ActiveSheet
    Set chChart = ActiveSheet.ChartObjects(1)
    For i = 1 To 5
        Select Case i
            Case 1: c1 = 5
            Case 2: c1 = 6
            Case 3: c1 = 7
            Case 4: c1 = 1
            Case 5: c1 = 13
        End Select
        Worksheets.Add
        chChart.Copy
        ActiveSheet.Paste
        nSeries = 0
        r1 = 1 ' r1 = first row in the HP range
        r2 = 0 ' r2 = last row in the HP range
        Do While r1 <= wsChart.Cells(1, 1).CurrentRegion.Rows.Count
            If wsChart.Cells(r1, 4).Value = &quot;&quot; Then
                r1 = wsChart.Cells(r1, 4).End(xlDown).Row
                If r1 = wsChart.Cells.Rows.Count Then Exit Do
                r2 = wsChart.Cells(r1, 2).End(xlDown).Row
                If i = 5 Then
                    Set rngClock = Range(wsChart.Cells(r1 - 1, 11), wsChart.Cells(r2, 11))
                Else
                    Set rngClock = Range(wsChart.Cells(r1 - 1, 4), wsChart.Cells(r2, 4))
                End If
                nSeries = nSeries + 1
                PlotSeries nSeries, Range(wsChart.Cells(r1 - 1, c1), wsChart.Cells(r2, c1))
                r1 = r2 + 1
            End If
        Loop
    Next
End Sub
Sub PlotSeries(n, rng)
    With ActiveSheet.ChartObjects(1).Chart
        If n > .SeriesCollection.Count Then
            .SeriesCollection.Add Source:=rng.Address(external:=True)
        Else
            For i = .SeriesCollection.Count To n + 1 Step -1
                .SeriesCollection(.SeriesCollection.Count).Delete
            Next
        End If
        .SeriesCollection(n).XValues = rngClock
        .SeriesCollection(n).Values = rng
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xitu]

Skip,

I got it


.
.
.
CHOICE = InputBox("Make your choice", "Input required&quot

Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
.
.
.

nSeries = nSeries + 1

Select Case CHOICE
Case 1
PlotClockDiffP nSeries
Case 2
PlotClockDiffTemp nSeries
Case 3
PlotClockPercent nSeries
Case 4
PlotClockPressure nSeries
Case 5
PlotClockTemperature nSeries
Case 6
PlotCmbTotalPressure nSeries
End Select
.
.
.


and it worked great!
...but I don't know how to have a long text in the inputbox
ex: Enter 1 for PlotClockDiffP
Enter 2 for PlotClockDiffTemp
.
.

Many thanks for all your help.
XT