VBA Chart and Array

[Mark1]

Anybody know how I can reference an array that's values will be equal to the
values that a chart is using? Here's what I'm attempting to do and I don't
know if it's possible:

I've got a bunch of sheets and a chart on each one. The data series values
on each chart is set to a range of cells in each sheet. I want to sort that
range of cells from highest to lowest on each sheet. The problem is that the
range on one page doesn't have anything in common with the range on the next
page except that they are both used in their respective Charts as the data
series values. So, I can't loop through each page and say sort
Range("A1:A5") because on the next page it might be Range("B1:B5") that needs
to be sorted. They don't have the same column heading either. So, I need to
loop through each page and tell VBA to SORT THE DATA THAT THE CHART ON THE
CURRENT PAGE IS USING. Thanks!!!

 

[Bernie Deitrick]

Mark,

Try the macro below. Assumes you are only charting one line, in a line
chart.

HTH,
Bernie
MS Excel MVP

Sub MarksMacro()
Dim mySht As Worksheet
Dim iStrStart As Integer
Dim iStrEnd As Integer
Dim myRef As String
Dim myAdd As String

For Each mySht In ActiveWorkbook.Worksheets
mySht.Select
ActiveSheet.ChartObjects(1).Activate
myRef = ActiveChart.SeriesCollection(1).Formula
iStrStart = InStr(1, myRef, "!") + 1
iStrEnd = InStr(iStrStart, myRef, ",") - 1
myAdd = Mid(myRef, iStrStart, iStrEnd - iStrStart + 1)
MsgBox "I will now sort the range " & _
Range(myAdd).Address(False, False, xlA1, True)
Range(myAdd).Sort Range(myAdd)(1), xlDescending, Header:=xlYes
Next mySht
End Sub