Defining data series for bubble chart

[stephj]

Hi all,

I'm trying to make a macro for making a bubble chart where the columns that are used for the data are dependent on the header in the top row. I have code that will work with defined colums, and code to determine which columns are wanted (both below and working fine) but I don't know how to use the different columns each time the code is run, as I seem to have ended up with cell references in different formats. Any help would be greatly appreciated.

Code for making the bubble chart with data from defined columns.

    With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=600, Top:=200, Height:=500)
            .Chart.ChartType = xlBubble
            .Chart.SetSourceData Source:=Range( _
        "'data'!$B:$B,'data'!$H:$H,'data'!$O:$O")

Code for finding which column is desired:

Do While Cells(1, i).Value <> False
    If Cells(1, i).Text = "Start date" Then intStartDateRow = i
    i = i + 1
Loop

(Using Excel 2007 on Windows 7)

 

[stephj]

No ideas at all? I'm really stuck here!

 

[stephj]

All I think I need is a way of making a bubble chart using cell references instead of range. Does anyone know how to do this please? (Although any other way would be great too!)

 

[stephj]

I asked the question on ozgrid.com today and got an answer, in case anyone with a similar problem searches up this thread

http://www.ozgrid.com/forum/showthread.php?t=156937