end of pivot table?

[Kris Erickson]

Hello, I'm looking for the end of the pivot table because I'm making a scatterplot on some data on the pivot table (which it won't let me normally do normally). Anyways I want to know when the end of the Pivot table occurs, so I can stop the for loop. And when the end of the most left entry of the pivot table ends, (When the next row field starts) and add a new series with each new 'topic' of the row field.

I have a few columns outside of the pivot table that are T/F and look for "total" and "Grand Total" and it gives me the right results.

But I was curious if there was an actual pivot table funtion that I could use. If not, I have the problem solved, but I was curious.

Thanks

 

[Norie]

Kris

I take it you are doing this in code?

If you are you probably want to take a look at the following properties of a pivot table:

ColumnRange
RowRange
DataBodyRange
DataLabelRange
PageRange

I'm not particularly familiar with them myself but take a look in the help files for more info.

 

[Kris Erickson]

Making a Scatterplot from Pivot Table

Aw poor me, there's not help installed on this computer! I'll look for explainations later. Well it's 5:30 I gotta peace out. I'll finish this later. Any suggestions as how to make a scatterplot outta pivot data, please feel free to post.

I have data to the left, and 2 columns far to the right outside of the pivot table that tell me when the row data ends, and the pivot table ends. I want to keep adding data until a new row field comes, and then add a new series with the new row field item, and then stop when the pivot table is done. (They both display TRUE when i'm in the row that I need to stop at)

So far I have this and i'm piecing it together from what i'm reading from other places:

Sub ScatterNation()

Set cht = Sheet2.ChartObjects.Add(100, 100, 350, 225).Chart
cht.ChartType = xlXYScatter

' remove series and start from scratch
Do While cht.SeriesCollection.Count > 1
cht.SeriesCollection(cht.SeriesCollection.Count).Delete
Loop

ActiveSheet.Select (Sheet2)
ActiveChart.Select (cht)

X = 1
y = 1
a = 1
b = 1
'2 loops one adds news series, and the other adds new data to that series
'
While ActiveSheet.Range.FormulaR1C1(y, X) = False
'this loop should make a new series

cht.SeriesCollection.NewSeries
cht.seriescollection.Name = (new pivot item on the far left column)


While ActiveSheet.Range.FormulaR1C1(a, b) = False
'this loop should add data to the prevous series
cht.SeriesCollection(X).XValues = (same row left column)
cht.SeriesCollection(X).Values = (same row right column)

End

 

[Kris Erickson]

Pivottable and ScatterPlot

I have made some code that works for me.

It makes a scatter plot out of pivot table data.
The first pivot table data title on B32,
It's x data at D32 and y at E32.
C32 is a unique number that each entry has, it was made a row field, resulting in a unique x and y value.

I used average for the pivot table data, but max or min will give the same thing. Note that 'total' is included in the plot, it didn't matter to me and I liked having it.

G32 has the control loop for the first 'subdivision' of the data field (i don't know how to explain what 'data field' is in proper lingo, but it's the left part of the Pivot Table) (column G has "=NOT(ISERR(SEARCH("total",B31,1)))" for the formula), and H32 has the control value that d returns for the end of the pivot table (col H has "=NOT(ISERR(SEARCH("grand total",B31,1)))")

I guess my next goal is to remove the formulas from the excel sheet and incorperate it into the macro. And to add a pic of the sheet to show where my data is at.

Sub scatterpivot()
x = 1
row1 = 33
rowstart = 32
col1 = 2
col2 = 7
Dim y As Integer
y = 1
Dim testt As String  ' the PT leftmost item, im using it as the series title
Dim exit1 As Boolean
Dim exit2 As Boolean
Dim displ As Chart

  ' next 7 lines made by Jon Peltier
  ' add chart
  Set displ = Sheet2.ChartObjects.Add(100, 100, 350, 225).Chart
  displ.ChartType = xlXYScatter
  ' remove series and start from scratch
  Do While displ.SeriesCollection.Count > 1
    displ.SeriesCollection(displ.SeriesCollection.Count).Delete
  Loop


testt = Sheet2.Cells(row1, col1)
exit1 = Sheet2.Cells(row1, (col2 + 1))
exit2 = Sheet2.Cells(row1, col2)

While exit1 = False
'this for loop adds the series
Debug.Print (testt & " loop1")
testt = Sheet2.Cells(row1 - 1, col1)

    While exit2 = False
        'for loop finding the length of the data area
        row1 = row1 + 1
        exit2 = Sheet2.Cells(row1, col2)
    Wend

    If Sheet2.Cells(row1 - 1, col2) = False Then
        'this if
        displ.SeriesCollection.NewSeries
        Debug.Print (row1 - 1 & " ...." & col1 + 2)
        displ.SeriesCollection(y).Name = testt
        'adding data to series here
        y = y + 1
        displ.SeriesCollection(y - 1).XValues = Sheet2.Range("D" & rowstart, "D" & row1)
        displ.SeriesCollection(y - 1).Values = Sheet2.Range("E" & rowstart, "E" & row1)
    End If

rowstart = row1
exit1 = Sheet2.Cells(row1, (col2 + 1))
exit2 = Sheet2.Cells(row1, col2)
row1 = row1 + 1

Wend
End Sub

 

[Kris Erickson]

Oh wait, I checked it with more data, and I have some error. Let me keep thinking about it. I will try to make the code error free and quicker