I'm still trying to work out the same problem: how to process data from an Excel sheet (above all sort through it but in groups; will explain further down) to use in a Cluster Chart without storing it as data in an Excel sheet.
The problem at hand:
I have a table somewhat like this...
A B E K Y 4 Group One Green 5 3 3 5 Yellow 3 7 4 6 Blue 7 6 2 7 Grey 9 1 8 8 Group Two High 1 5 1 9 Low 2 6 6 10 Group Three Small 5 1 2 11 Large 1 1 5
I left out the rows and columns that contain data irrelevant for the chart (like rows 1 to 3, columns C, D, F to J, et).
What I need to do is create (in this example) three cluster charts, one with the ranges A4:B11;E4:E11, the second with A4:B11;K4:K11 and the third with A4:B11;Y4:Y11. Data has to be sorted for each column, from row 4 to 7, from 8 to 9 and from 10 to 11, but has to appear in the same chart.
The data for the first would have to sorted like that:
A B E 4 Group One Grey 9 5 Blue 7 6 Green 5 7 Yellow 3 8 Group Two Low 2 9 High 1 10 Group Three Small 5 11 Large 1
One of the reasons that I don't want to put the data in another range, sort it there and use that for the chart is that I have to create many charts, and I literally mean many.
I searched to internet, only to find merely half-solution like creating charts with a macro and attributing a range to
Chart.SetSourceData but how to sort that range first (in those three group^s described above) I haven't figured out yet. (See my Excel VBA: Array to Range (for SetSourceData) thread.)
Or I tried to apply an SQL statement but couldn't figure how to achieve that. (See my SQL Query within Excel thread.)
Additionally I don't get neither the Sort method to work (naturally all examples imply the use on an actual range on a sheet and not a stored range collection. But the original datasheet may not be sorted!
I tried using
Sort like this:
Dim rdt As Range Dim r1 As Range Dim myColl As Range Set rdt = Range("B4:B30") Set r1 = Range("G4:G30") Set myColl = Union(rdt, r1) myColl.Sort Key1:=[B]X[/B], Order1:=xlDescending, Key2:=[B]Y[/B], Order2:=xlAscending
But I don't know how to define the Key.
I also tried sorting through the range by using a function. It doesn't work either:
Public Function TriTableau(MyVar()) Dim i As Integer Dim j As Integer Dim tempA Dim tempB For i = 1 To 26 For j = 2 To 27 If MyVar(i, 2) < MyVar(j, 2) Then tempA = MyVar(i, 1) tempB = MyVar(i, 2) MyVar(i, 1) = MyVar(j, 1) MyVar(i, 2) = MyVar(j, 2) MyVar(j, 1) = tempA MyVar(j, 2) = tempB End If Next j Next i TriTableau = MyVar End Function
Which is adapted from a French book by Mikaël Bidault, Excel & VBA XP, published 2005.
Again, it won't work, neither on the stored range collection itself nor on an array created from the range.
And before you ask: I tried LINQ as well, the Visual Basic Editor has right from the first step a problem, when creating the query like this
Dim evensQuery = From num In numbers Order By num Select num the editor stops at "=" expecting the end of the instruction.
As I'm trying to solve the problem for two days now, I'd really appreciate any help you could offer.