Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Please help with Excel Chart 1

Status
Not open for further replies.

xitu

Technical User
Oct 1, 2003
55
US
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").Range(CellAddr), PlotBy _:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

End Sub


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

I believe I posted an answer to that in the code I posted.
Code:
'From the calling routine...
Set rngTime = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
Set rngPressure = Range(Cells(r + r1 - 1, 5), Cells(r2, 5))

Sub FormatSeries(n)
    With ActiveSheet.ChartObjects(1).Chart
        If n > .SeriesCollection.Count Then
            .SeriesCollection.Add Source:=rngPressure.Address(external:=True)
        Else
            For i = 2 To n
                .SeriesCollection(i).Delete
            Next
        End If
        .SeriesCollection(n).XValues = rngTime
        .SeriesCollection(n).Values = rngPressure
    End With
End Sub
You never asked me what you did not understand about my code. I did volumteer to explain.



Skip,
Skip@TheOfficeExperts.com
 
Skip,

You are the best helper I have ever met.

I spent last couple days to study your code. I understood some of them... One part of your code that I did not understand:

Set rngTime = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
Set rngPressure = Range(Cells(r + r1 - 1, 5), Cells(r2, 5))

Anyway, the last thing I need is to plot the Series. I have marked all the Start-End blocks already. How do I code for the FormatSeries Sub independently?

Thanks,
XT





 
r1 & r2 are the first and last row in the HP range

r is the OFFSET within that range of rows where the MAX pressure is found

r = Application.Match(nMAX, rng, 0)

So the row range for time and pressure are from r1 + r -1 to r2.

So if you want to retrofit FormatSeries, you can use the same technique of for a column starting at row 1 and using the End method to find the first row (r1 value) and then having found the first row use the End method again to find the last row of contiguous data in that range (r2). (see the callin routine and modify for col D)

I have to leave for home. I'll pick it up in the AM.

:)

Skip,
Skip@TheOfficeExperts.com
 
It's better, Skip.

Now I can understand your code > 75 %.

I have a quick question:
Since your code does not have function to mark "HP":

Then I built a function to detect "HP" (where value > 11300) but how do I detect if the value in current row -(minus) the value in next row > 50 then do not mark "HP" ?


Sub MarkHP()
Dim d As Range
Dim iSeconds As Integer
Dim iTime As Double

Application.ScreenUpdating = False
For Each d In Intersect(ActiveSheet.UsedRange, Range(A:A))
If d.Value > 0 Then
' Set clock start for the whole worksheet 0:00:00 with increment is 3 in colum L
iTime = iSeconds / 86400
d.Offset(0, 11) = WorksheetFunction.Text(iTime, "h:mm:ss")
iSeconds = iSeconds + 3

If d.Value > 11300 Then
' Mark "HP"
d.Offset(0, 1) = ""HP""
'I tried the below code but it didn't work
' If d.Offset(0, 0).Value - d.Offset(1, 0).Value > 30 Then
' d.Offset(0, 1) = ""
' End If

End If
End If
Next d

Application.ScreenUpdating = True
End Sub



For example

=======================================
10966
11176
11267
11305 HP
11404 HP
11477 HP
11485 HP "MAX" 0:00:00
11442 HP 0:00:03
11451 HP 0:00:06
11444 HP 0:00:09
11443 HP 0:00:12
11437 HP 0:00:15
11446 HP 0:00:18
11444 HP "END" 0:00:21
11344
11327
11319
9301
8962
8653
8335
8030
7735
7376
8653
8335
8030
7735
7376
6874
6155
11387 HP
11390 HP "MAX" 0:00:00
11388 HP 0:00:03
11386 HP 0:00:06
11386 HP 0:00:09
11385 HP 0:00:12
11385 HP 0:00:15
11384 HP 0:00:18
11384 HP 0:00:21
11385 HP 0:00:24
11384 HP 0:00:27
11384 HP "END" 0:00:30
11320
11318
11317
8335
8030
7735
7376
8335
8030
7735
7376
=======================================

Another issue:

Do you have any idea with the below code? Everytime I make the Series, I have to make a blank chart, then uncomment one of these:


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





Sub PlotCharts()

Dim r1, r2, t, nSeries As Integer
Dim rng As Range
Dim nMax As Double
Dim r As Variant
Dim pBase As Double
Dim c As Range

r1 = 1 ' r1 = first row in the HP range
r2 = 0 ' r2 = last row in the HP range
nSeries = 0
Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
If Cells(r1, 2).Value = &quot;&quot; Then
r1 = Cells(r1, 2).End(xlDown).Row
If r1 = Cells.Rows.Count Then Exit Do
r2 = r1

If Cells(r1, 1).Offset(0, 0).Value - Cells(r1, 1).Offset(0, 0).Value > 30 Then
Cells(r1, 2).Offset(1, 0).Value = &quot;&quot;
End If
If Cells(r1, 2).Offset(1, 0).Value = &quot;HP&quot; Then
r2 = Cells(r1, 2).End(xlDown).Row
End If

Set rng = Range(Cells(r1, 1), Cells(r2, 1))
nMax = Application.Max(rng)
r = Application.Match(nMax, rng, 0)
' r is OFFSET within range of rows where START is found
If Not IsError(r) Then
Cells(r + r1 - 1, 3).Value = START_SYMBOL
Cells(r2, 3).Value = END_SYMBOL
t = 0
Set rngClock = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
Set rngDiffP = Range(Cells(r + r1 - 1, 5), Cells(r2, 5))
Set rngDiffTemp = Range(Cells(r + r1 - 1, 6), Cells(r2, 6))
Set rngPercent = Range(Cells(r + r1 - 1, 7), Cells(r2, 7))
Set rngPressure = Range(Cells(r + r1 - 1, 1), Cells(r2, 1))
Set rngTemperature = Range(Cells(r + r1 - 1, 13), Cells(r2, 13))
Set rngCmbTotal = Range(Cells(r + r1 - 1, 11), Cells(r2, 11))
pBase = Cells(r + r1 - 2, 1).Value
For Each c In rngClock
With c
.Value = t
.NumberFormat = &quot;[h]:mm:ss&quot;
.Offset(0, 1).Value = pBase - Cells(.Row, 1).Value
End With
t = t + 3 / SECONDS_PER_DAY
Next
nSeries = nSeries + 1

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



End If
r1 = r2 + 1
End If
Loop
End Sub
Sub PlotClockDiffP(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngDiffP.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngDiffP
End With
End Sub

Sub PlotClockDiffTemp(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngDiffTemp.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngDiffTemp
End With
End Sub


Sub PlotClockPercent(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngPercent.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngPercent
End With
End Sub

Sub PlotClockPressure(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngPressure.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngPressure
End With
End Sub



Sub PlotClockTemperature(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngTemperature.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngTemperature
End With
End Sub

Sub PlotCmbTotalPressure(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngPressure.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngCmbTotal
.SeriesCollection(n).Values = rngPressure
End With
End Sub




Many thanks,
XT
 
What seems to happen when &quot;HP&quot; is turned off is this...
Code:
11446   &quot;HP&quot;  &quot;END&quot;
11444
Code:
11344   &quot;HP&quot;
11327   &quot;HP&quot;
Code:
11319
9301
8962
the value is > 11300 AND the pressure difference is >50

So you may have an error in your algorythm.

Skip,
Skip@TheOfficeExperts.com
 
I'm OK with the condition for the pressure now :)

Do you have any idea with the Series?

Thanks,
XT

P.S. Believe it or not, I just knew OFFSET in Excel last week.
 
I am leaving now, too.

Catch you tomorrow.
 
1. fixed the HP marking

2. modified PlotCharts to use an array for the series ranges

3. modified the PlotSeries to do all series called from a loop
Code:
Dim rngClock As Range
Sub MarkHP()
    Dim d As Range
    Dim iSeconds As Integer
    Dim iTime As Double
    
    Application.ScreenUpdating = False
    For Each d In Intersect(ActiveSheet.UsedRange, Range(&quot;A:A&quot;))
        If d.Value > 0 Then
        ' Set clock start for the whole worksheet 0:00:00 with increment is 3 in colum L
            iTime = iSeconds / 86400
            d.Offset(0, 11) = WorksheetFunction.Text(iTime, &quot;h:mm:ss&quot;)
            iSeconds = iSeconds + 3
            
             If d.Value > 11300 Then
               ' Mark &quot;HP&quot;
               d.Offset(0, 1) = &quot;&quot;&quot;HP&quot;&quot;&quot;
    'I tried the below code but it didn't work
               If d.Offset(0, 0).Value - d.Offset(1, 0).Value > 50 Then
                 d.Offset(0, 1) = &quot;&quot;
               End If
             End If
        End If
    Next d
    
    Application.ScreenUpdating = True
End Sub
Sub PlotCharts()
    Dim rngSeries(0 To 5) As Range      'ADDED
    Dim r1, r2, t, nSeries As Integer
    Dim rng As Range
    Dim nMax As Double
    Dim r As Variant
    Dim pBase As Double
    Dim c As Range
   
    r1 = 1 ' r1 = first row in the HP range
    r2 = 0 ' r2 = last row in the HP range
    nSeries = 0
    Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        If Cells(r1, 2).Value = &quot;&quot; Then
            r1 = Cells(r1, 2).End(xlDown).Row
            If r1 = Cells.Rows.Count Then Exit Do
            r2 = r1
          
              If Cells(r1, 1).Offset(0, 0).Value - Cells(r1, 1).Offset(0, 0).Value > 30 Then
                 Cells(r1, 2).Offset(1, 0).Value = &quot;&quot;
              End If
               If Cells(r1, 2).Offset(1, 0).Value = &quot;HP&quot; Then
                 r2 = Cells(r1, 2).End(xlDown).Row
               End If
           
            Set rng = Range(Cells(r1, 1), Cells(r2, 1))
            nMax = Application.Max(rng)
            r = Application.Match(nMax, rng, 0)
            ' r is OFFSET within range of rows where START is found
            If Not IsError(r) Then
                Cells(r + r1 - 1, 3).Value = START_SYMBOL
                Cells(r2, 3).Value = END_SYMBOL
                t = 0
         'NEW CODE
                Set rngClock = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
                Set rngSeries(0) = Range(Cells(r + r1 - 1, 5), Cells(r2, 5))
                Set rngSeries(1) = Range(Cells(r + r1 - 1, 6), Cells(r2, 6))
                Set rngSeries(2) = Range(Cells(r + r1 - 1, 7), Cells(r2, 7))
                Set rngSeries(3) = Range(Cells(r + r1 - 1, 1), Cells(r2, 1))
                Set rngSeries(4) = Range(Cells(r + r1 - 1, 13), Cells(r2, 13))
                Set rngSeries(5) = Range(Cells(r + r1 - 1, 11), Cells(r2, 11))
        '^TO HERE
                pBase = Cells(r + r1 - 2, 1).Value
                For Each c In rngClock
                    With c
                        .Value = t
                        .NumberFormat = &quot;[h]:mm:ss&quot;
                        .Offset(0, 1).Value = pBase - Cells(.Row, 1).Value
                    End With
                    t = t + 3 / 86400
                Next
        'NEW CODE
                For i = LBound(rngSeries, 1) To UBound(rngSeries, 1)
                    nSeries = nSeries + 1
                    PlotSeries nSeries, rngSeries(i)
                Next
        '^TO HERE
            End If
            r1 = r2 + 1
        End If
    Loop
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
 
I cleaned up the PlotChart routine...
Code:
Sub PlotCharts()
    
    Dim r1, r2, t, nSeries As Integer
    Dim rng As Range
    Dim nMax As Double
    Dim r As Variant
    Dim pBase As Double
    Dim c As Range
   
    r1 = 1 ' r1 = first row in the HP range
    r2 = 0 ' r2 = last row in the HP range
    nSeries = 0
    Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        If Cells(r1, 2).Value = &quot;&quot; Then
            r1 = Cells(r1, 2).End(xlDown).Row
            If r1 = Cells.Rows.Count Then Exit Do
            r2 = r1
          
              If Cells(r1, 1).Offset(0, 0).Value - Cells(r1, 1).Offset(0, 0).Value > 30 Then
                 Cells(r1, 2).Offset(1, 0).Value = &quot;&quot;
              End If
               If Cells(r1, 2).Offset(1, 0).Value = &quot;HP&quot; Then
                 r2 = Cells(r1, 2).End(xlDown).Row
               End If
           
            Set rng = Range(Cells(r1, 1), Cells(r2, 1))
            nMax = Application.Max(rng)
            r = Application.Match(nMax, rng, 0)
            ' r is OFFSET within range of rows where START is found
            If Not IsError(r) Then
                Cells(r + r1 - 1, 3).Value = START_SYMBOL
                Cells(r2, 3).Value = END_SYMBOL
                t = 0
                
                Set rngClock = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
                pBase = Cells(r + r1 - 2, 1).Value
                For Each c In rngClock
                    With c
                        .Value = t
                        .NumberFormat = &quot;[h]:mm:ss&quot;
                        .Offset(0, 1).Value = pBase - Cells(.Row, 1).Value
                    End With
                    t = t + 3 / 86400
                Next
        'NEW CODE
                For i = 1 To 6
                    Select Case i
                        Case 1: c1 = 5
                        Case 2: c1 = 6
                        Case 3: c1 = 7
                        Case 4: c1 = 1
                        Case 5: c1 = 13
                        Case 6: c1 = 11
                    End Select
                    nSeries = nSeries + 1
                    PlotSeries nSeries, Range(Cells(r + r1 - 1, c1), Cells(r2, c1))
                Next
        '^TO HERE
            End If
            r1 = r2 + 1
        End If
    Loop
End Sub


Skip,
Skip@TheOfficeExperts.com
 
Skip,

Thanks for your big help.

Back to your code, I do not want to make Series for
PlotClockDiffP, PlotClockDiffTemp, PlotClockPercent, PlotClockPressure, PlotClockTemperature, PlotCmbTotalPressure.

I would like to make series for each above list seperately (in different charts)

Since my code is too long now, please take a look at

Thanks again,
XT
 
Different embedded charts on separate sheets (Each chart will be add in a new sheet)

Thanks,
XT
 
How's this?
Code:
Dim rngClock As Range
Dim chChart As ChartObject
Dim wsChart As Worksheet
Sub PlotCharts()
    
    Dim r1, r2, t, nSeries As Integer
    Dim rng As Range
    Dim nMax As Double
    Dim r As Variant
    Dim pBase As Double
    Dim c As Range
   
    r1 = 1 ' r1 = first row in the HP range
    r2 = 0 ' r2 = last row in the HP range
    nSeries = 1
    Set wsChart = ActiveSheet
    Set chChart = ActiveSheet.ChartObjects(1)
    Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        If Cells(r1, 2).Value = &quot;&quot; Then
            r1 = Cells(r1, 2).End(xlDown).Row
            If r1 = Cells.Rows.Count Then Exit Do
            r2 = r1
          
              If Cells(r1, 1).Offset(0, 0).Value - Cells(r1, 1).Offset(0, 0).Value > 30 Then
                 Cells(r1, 2).Offset(1, 0).Value = &quot;&quot;
              End If
               If Cells(r1, 2).Offset(1, 0).Value = &quot;HP&quot; Then
                 r2 = Cells(r1, 2).End(xlDown).Row
               End If
           
            Set rng = Range(Cells(r1, 1), Cells(r2, 1))
            nMax = Application.Max(rng)
            r = Application.Match(nMax, rng, 0)
            ' r is OFFSET within range of rows where START is found
            If Not IsError(r) Then
                Cells(r + r1 - 1, 3).Value = START_SYMBOL
                Cells(r2, 3).Value = END_SYMBOL
                t = 0
                
                Set rngClock = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
                pBase = Cells(r + r1 - 2, 1).Value
                For Each c In rngClock
                    With c
                        .Value = t
                        .NumberFormat = &quot;[h]:mm:ss&quot;
                        .Offset(0, 1).Value = pBase - Cells(.Row, 1).Value
                    End With
                    t = t + 3 / 86400
                Next
        'NEW CODE
                For i = 1 To 6
                    Select Case i
                        Case 1: c1 = 5
                        Case 2: c1 = 6
                        Case 3: c1 = 7
                        Case 4: c1 = 1
                        Case 5: c1 = 13
                        Case 6: c1 = 11
                    End Select
                    wsChart.Activate
'                    nSeries = nSeries + 1
                    PlotSeries nSeries, Range(Cells(r + r1 - 1, c1), Cells(r2, c1))
                Next
        '^TO HERE
            End If
            r1 = r2 + 1
        End If
    Loop
End Sub
Sub PlotSeries(n, rng)
    Worksheets.Add
    chChart.Copy
    ActiveSheet.Paste
    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
 
Yes, your code creates different charts in different sheets. However, it does not add series for each chart.

Is there anyway I can finish making one series (PlotClockDiffP) first then do the next series(PlotClockDiffTemp)?

Please look at the code below, if I run the PlotClockDiffP then comment it and uncomment the PlotClockDiffTemp, and so on...., I will have exactly what I wish to have. Do you have any idea for prevent the comment, uncomment steps?


Dim rngClock As Range
Dim rngDiffP As Range
Dim rngDiffTemp As Range
Dim rngPercent As Range
Dim rngPressure As Range
Dim rngTemperature As Range
Dim rngCmbTotal As Range
Dim CHART_TITLE As String
Dim X_TITLE As String
Dim Y_TITLE As String

Sub PlotCharts()

r1 = 1 ' r1 = first row in the HP range
r2 = 0 ' r2 = last row in the HP range
nSeries = 0


Sheets(&quot;Data&quot;).Select
Range(&quot;R1&quot;).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets(&quot;Data&quot;).Range(&quot;R1&quot;)
ActiveChart.Location Where:=xlLocationAsObject, Name:=&quot;Data&quot;


Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
Range(&quot;A1&quot;).Select
If Cells(r1, 2).Value = &quot;&quot; Then
r1 = Cells(r1, 2).End(xlDown).Row
If r1 = Cells.Rows.Count Then Exit Do
r2 = r1
If Cells(r1, 2).Offset(1, 0).Value = &quot;HP&quot; Then
r2 = Cells(r1, 2).End(xlDown).Row
End If
Set rng = Range(Cells(r1, 1), Cells(r2, 1))
nMax = Application.Max(rng)
r = Application.Match(nMax, rng, 0)
' r is OFFSET within range of rows where START is found
If Not IsError(r) Then
Set rngClock = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
Set rngDiffP = Range(Cells(r + r1 - 1, 5), Cells(r2, 5))
Set rngDiffTemp = Range(Cells(r + r1 - 1, 6), Cells(r2, 6))
Set rngPercent = Range(Cells(r + r1 - 1, 7), Cells(r2, 7))
Set rngPressure = Range(Cells(r + r1 - 1, 1), Cells(r2, 1))
Set rngTemperature = Range(Cells(r + r1 - 1, 13), Cells(r2, 13))
Set rngCmbTotal = Range(Cells(r + r1 - 1, 11), Cells(r2, 11))

nSeries = nSeries + 1

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


End If
r1 = r2 + 1
End If
Loop

ActiveSheet.ChartObjects(1).Select
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveWindow.Zoom = 100
AddTitle
Application.ScreenUpdating = True
End Sub
Sub PlotClockDiffP(n)

'here

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngDiffP.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngDiffP
'.SeriesCollection(n).Name = &quot;=&quot;&quot;High Pressure&quot;&quot;&quot;
End With

End Sub

Sub PlotClockDiffTemp(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngDiffTemp.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngDiffTemp

End With
End Sub


Sub PlotClockPercent(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngPercent.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngPercent
End With
End Sub

Sub PlotClockPressure(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngPressure.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngPressure
End With
End Sub



Sub PlotClockTemperature(n)

With ActiveSheet.ChartObjects(1).Chart
If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngTemperature.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngClock
.SeriesCollection(n).Values = rngTemperature
End With
End Sub

Sub PlotCmbTotalPressure(n)

With ActiveSheet.ChartObjects(1).Chart
ActiveChart.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle = &quot;TEST &quot;

If n > .SeriesCollection.Count Then
.SeriesCollection.Add Source:=rngPressure.Address(external:=True)
Else
For i = 2 To n
.SeriesCollection(i).Delete
Next
End If
.SeriesCollection(n).XValues = rngCmbTotal
.SeriesCollection(n).Values = rngPressure
End With
End Sub

Sub AddTitle()
If Not ActiveChart Is Nothing Then
With ActiveChart
.HasTitle = True
CHART_TITLE = InputBox(&quot;Chart Title?&quot;, &quot;Input required&quot;)
.ChartTitle.Text = CHART_TITLE
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
X_TITLE = InputBox(&quot;Category (X) Axis?&quot;, &quot;Input required&quot;)
.AxisTitle.Text = X_TITLE '&quot;Category (X) Axis&quot;
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
Y_TITLE = InputBox(&quot;Value (Y) Axis?&quot;, &quot;Input required&quot;)
.AxisTitle.Text = Y_TITLE '&quot;Value (Y) Axis&quot;
End With
End With
Else
MsgBox &quot;Please select a chart and try again.&quot;, _
vbExclamation, &quot;No Chart Selected&quot;
End If
End Sub


Thanks,
XT
 
Yes, I worked on my real data which has all columns. Each chart (in new a sheet) has only the first range in series.

The reason why I had to separate into individual functions because sometimes I have to plot series for rngPercent vs. rngPressure. It isn't always plot rngClock vs. others.

Have you looked my file at
Thanks,
XT
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top