VBA Dynamic Cell Referencing for Charts title

Hi,

I need a solution for a problem which might seem simpler to you, but I’ve no idea where to go! I have plotted several charts in a excel sheet, now I want to give an individual title to each chart, and since each chart has its own particular data range, it also has a dedicated column from where it can pull text from a cell to update its title. For e.g. the first chart can pull cell value from column A (2nd row), second chart can pull from column K (2nd row) and so on.

I have a simple presentation of the chart title code here:

With chtChart
            .ChartType = xlXYScatterLines
            '.Name = Cells(2, j - 2).Name
            '.ChartTitle.Text = ActiveSheet.range(Cells(2, j-2)
End With

How would you solve this evasive problem? I tried two ways to dynamically reference the cells for chart title but no success fell upon me so far! j is the column loop (For) and j-2 is the column (for each plotted chart) where we have the text values for the charts to pull its title from!

Thanks alot!
Sanjeev

Well, there might be a question why I don't do it manually! Its because I have 8 charts per sheet for 14 sheets in a workbook, and i'll have 4 workbooks! So, I really need your help as the excel gurus have done it before!

Try something like this. Add the chart's data series before you add the chart's title.

With chtChart
    .ChartType = xlXYScatterLines
    .Parent.Name = Cells(2, j - 2).Text
    'First add the data series here
    .HasTitle = True
    .ChartTitle.Text = ActiveSheet.Cells(2, j - 2).Text
End With

Hey AlphaFrog, you must be an angel, your solution worked like a swift breeze. Thank you so much!