Hi, how do i code it such that i get the chart as shown below. this was done via manual selection of data:
the current code i have does not give me the result i want:
Any help is appreciated
the current code i have does not give me the result i want:
VBA Code:
With ws3
.Range("A1:AE" & lrow3).Sort key1:=Range("B1:B" & lrow3), key2:=Range("C1:C" & lrow3), _
order1:=xlAscending, Header:=xlYes
For x = 2 To lrow3
a = WorksheetFunction.CountIf(Range("B2:B" & lrow3), "PP1")
Next
For x = 2 To lrow3
b = WorksheetFunction.CountIf(Range("B2:B" & lrow3), "PP2")
Next
i = findValues("first", "PP1", Range("B2:B" & lrow3))
Set rng1 = ws3.Range("C" & i & ":C" & a + i - 1)
Set rng2 = ws3.Range("D" & i & ":D" & a + i - 1)
Set rng3 = ws3.Range("F" & i & ":F" & a + i - 1)
j = findValues("first", "PP2", Range("B2:B" & lrow3))
Set rng4 = ws3.Range("C" & i & ":C" & a + i - 1)
Set rng5 = ws3.Range("D" & i & ":D" & a + i - 1)
Set rng6 = ws3.Range("F" & i & ":F" & a + i - 1)
End With
ws2.Activate
ws2.Cells(55, 1).Select
'Create a chart
Set cht = ws2.ChartObjects.Add( _
Left:=ActiveCell.Left, _
Width:=450, _
Top:=ActiveCell.Top, _
Height:=250)
With cht.Chart
.SetSourceData Source:=Union(rng1, rng2, rng3, rng4, rng5, rng6), PlotBy:=xlColumns
.ChartType = xlXYScatterLines
.HasTitle = True
.ChartTitle.Text = "PP AVG PH Trend"
.Axes(xlValue).MinimumScale = 1000
' .Axes(xlValue).MaximumScale = 29
.SeriesCollection(1).Name = "PP1"
' .SeriesCollection(2).Name = "PP2"
End With
Any help is appreciated