Need to Multiple a Series Collection using VBA but without creating a new range in sheet

[sndameron]

I want to be able to plot the data in the first 2 columns. Then, in vba, I want to be able to plot it again, but multiply the second column (x-axis) by negative 1. I do not want to have to create a new column in my sheet of these negative values, though.

Y axis	X axis
45.370 45.710 45.782 46.208 46.564 46.744 46.779 47.079 47.295 47.494 47.600 47.799 48.311 48.346 48.352 48.394 48.435 48.476 <!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody> <!--EndFragment--></tbody>	29.24 37.79 61.96 76.72 71.35 61.67 52.01 52.21 37.29 61.52 58.95 36.66 55.14 47.99 29.09 35.28 50.34 49.06 <!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody> <!--EndFragment--></tbody>

<tbody>
</tbody>

Here is the code I have so far, but I have no idea how to do the second part:


With ActiveChart.SeriesCollection.Add
.Name = ActiveSheet.Range("B2")
.Values = ActiveSheet.Range("A2:A192")
.XValues = ActiveSheet.Range("B2:B192")
End With
'create negative x axis version (mirror it)
With ActiveChart.SeriesCollection.Add
.Name = ActiveSheet.Range("B2")
.Values = ActiveSheet.Range("A2:A192")
.XValues = -1*(ActiveSheet.Range(("B2:B192"))
End With

 

[Domenic]

The images at the bottom of your post are not being displayed. In any case, let's assume the following...

1. The sheet containing the data is the active sheet.
2. Column A contains the Y values.
3. Column B contains the X values.
4. A1 and B1 contain the column labels/headers.

Maybe something like this...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] CreateChart()

    [COLOR=green]'Declare the variables[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oChart      [COLOR=darkblue]As[/COLOR] Chart
    [COLOR=darkblue]Dim[/COLOR] vData       [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vTemp1      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vTemp2      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aYValues()  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aXValues()  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow     [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] j           [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'Make sure that there's an active worksheet[/COLOR]
    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) <> "Worksheet" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
    [COLOR=green]'Find the last used row[/COLOR]
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [COLOR=darkblue]If[/COLOR] LastRow > 1 [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=green]'Get the data[/COLOR]
        vData = Range("A2:B" & LastRow).Value
        
        [COLOR=green]'Redim the arrays[/COLOR]
        [COLOR=darkblue]ReDim[/COLOR] aYValues(1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1) * 2)
        [COLOR=darkblue]ReDim[/COLOR] aXValues(1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1) * 2)
        
        [COLOR=green]'Fill the arrays[/COLOR]
        j = 1
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)
            aYValues(j) = vData(i, 1)
            aXValues(j) = vData(i, 2)
            aYValues(j + 1) = vData(i, 1)
            aXValues(j + 1) = vData(i, 2) * -1
            j = j + 2
        [COLOR=darkblue]Next[/COLOR] i
        
        [COLOR=green]'Sort the arrays[/COLOR]
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aXValues) - 1
            [COLOR=darkblue]For[/COLOR] j = i + 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aXValues)
                [COLOR=darkblue]If[/COLOR] aXValues(i) > aXValues(j) [COLOR=darkblue]Then[/COLOR]
                    vTemp1 = aXValues(j)
                    vTemp2 = aYValues(j)
                    aXValues(j) = aXValues(i)
                    aYValues(j) = aYValues(i)
                    aXValues(i) = vTemp1
                    aYValues(i) = vTemp2
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] j
        [COLOR=darkblue]Next[/COLOR] i
        
        [COLOR=green]'Create the chart[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oChart = ActiveSheet.ChartObjects.Add(Left:=120, Top:=12, Width:=845, Height:=320).Chart
    
        [COLOR=green]'Set the properties for the chart[/COLOR]
        [COLOR=darkblue]With[/COLOR] oChart
            .ChartType = xlXYScatter
            [COLOR=darkblue]With[/COLOR] .SeriesCollection.NewSeries
                .Name = Range("B1").Value
                .XValues = aXValues
                .Values = aYValues
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        
    [COLOR=darkblue]Else[/COLOR]
    
        MsgBox "No data is available!", vbExclamation
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If
[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

Hope this helps!