Creating Chart with dynamic range size in VBA

[WoodEXCEL]

Here is the code.

The range will change for the column data. The data will always begin in
A21:B21 (two columns). How may rows will be dynamic (change with each VBA execution.

I keep getting an error relating the range set up below. How to get the syntax correct is my question.

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("TableQuery") _
.Range("A21", Range("A21:B21,End(xlDown)")), _
PlotBy:=xlColumns

Thank you much

 

[Andrew Poulsom]

Did you try?

ActiveChart.SetSourceData Source:=Selection

 

[Schroedinger]

Hi Andrew,

I've found this thread as I have a similar problem but because I'm just an excel user I find it very difficult to understand the code. So I apologize in advance if the answer is already here in this thread and I don't get it. Here is my problem. I managed to create a macro that looks like this:

Sub Macro1()
'
' Macro1 Macro
'


'
Range("G17:G36").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$G$17:$G$36")
ActiveChart.ChartType = xlLine
End Sub

I know this was pretty basic to record but my problem is how to change it and make the range dynamic and conditional. For example when I get to the row 17 I have a value in the cell D17 that is greater than lets say 200 and a value in E17 greater than 100. This should trigger the beginning of my range. So if D17>200 AND E17>100 I need to get G17 as the beginning of the range. As for G36 (the end of the range) the logic is very similar but this time I would test for a condition like this: IF F36<64 THEN get G36 as the end of the range.
The should repeat till the end. For example the last row could be at 28000 so I expect a good few of these charts to be created along the way.

Thanks is advance for your help,
Schroedinger.

 

[paulwarren]

I am having a similar problem to these. I inherited a workbook with a ton of VBA that I can sort of read and follow, but i only speak English!. It has several charts with fixed ranges with 391 data points per series and are located in different sheets. They are all generated from different columns in the same table (sheet3!$B$2:$T$393 including labels in row 2) and each has two vertical axis. for example one chart uses range =Sheet3!$B$2:$B$393,Sheet3!$R$2:$R$393 where the B range is a line chart and the R range is a column chart.

The table starts out blank and grows a line at a time, and the charts fill as the data is generated. For usability I need to show the same charts on a single page but only the most recently generated 10 lines (or less if there are less than 10). ( I need to retain the full 391 line charts as well)

I have duplicated each chart onto one sheet (named "last10charts") modifying the range to the current bottom 10. For example the range shown above is changed to =Sheet3!$B$384:$B$393,Sheet3!$R$384:$R$393. I now need code to dynamically change the range in each chart to the last 10 or less rows generated. The following code is generating the last columns of each row, so I know this is the last call for a given line of data. Can I add code to write2Exc that changes the range in each chart after the copies are run? Or call a new sub from there if that is better practice?

Thanks in advance for any help!

Public Sub write2Exc(i As Integer, sSheet As String)
Call copy2Exl("r", m_pBuySell, i, sSheet)
Call copy2Exl("s", m_pBuySellPos, i, sSheet)
Call copy2Exl("t", m_pProfLossUnrealized, i, sSheet)
End Sub

'copy to excel
Sub copy2Exl(strCol As String, pArr, i As Integer, sSheet As String) 'change to sub instead of function
Dim rEPUp As Range
Dim sCell As String
Dim rowExc As Integer
rowExc = i - cjStSize + 3
sCell = strCol & (rowExc)

Dim sEPUp As String
Dim dVal As Double
dVal = pArr(i)
If (dVal <> 0) Then
' rEPUp.Value = dVal
sEPUp = dVal
Else
' rEPUp.Value = ""
sEPUp = ""
End If
Sheets(sSheet).Range(sCell).Value = sEPUp
End Sub

 

[Schroedinger]

Hey Paul,

I'm not saying that the answer to your problem is on the following link but you can have a look at it and maybe it helps. After a good few hours of wait here I realized that I have to carry on looking for an answer so you'll also find the same question and two answers here:

excel - How to create multiple embedded line chart with dynamic range size using VBA - Stack Overflow

I still have to figure out a few things but at least I'm moving on.

Good luck!
Schroedinger.

 

[paulwarren]

Thanks Schroedinger

This doesnt seem like it should be that hard! Im trying to build it step by step, and am getting an "error 1004 application defined or object defined error" in the last line below.

Sub Macro1()
'
' Macro1 Macro
'


Dim lastRow As Integer
Dim newchrange As String
Sheets("last10charts").Select
lastRow = 24
ActiveSheet.ChartObjects("Whitech").Activate
newchrange = "Data!$B$18:$F$" & lastRow
ActiveChart.SetSourceData Source:=Sheets("Data").Range("newchrange")


End Sub

 

[paulwarren]

This works for the test file anyway...now to export it to the real thing! (I have no idea if anything here is redundant!)

Sub Macro1()
'
' Macro1 Macro
'


Dim lastRow As Integer
Dim firstRow As Integer

lastRow = 44
firstRow = lastRow - 9
ActiveSheet.ChartObjects("Whitech").Select
ActiveChart.SetSourceData Source:=Sheets("Data").Range(Sheets("Data").Cells(firstRow, "B"), Sheets("Data").Cells(lastRow, "D"))


End Sub

 

[Schroedinger]

This is the code I use to solve my problem. It might well help to someone else.

Sub GenerateCharts()    Application.ScreenUpdating = False
    Dim StartCell As Long
    Dim EndCell As Long
    Dim ChartRange As Range
    Dim DataEnd As Long
    Dim i As Integer
    Dim j As Integer
    Dim HasStart As Boolean
    Dim HasEnd As Boolean
    'Sets end of data based on the row you are charting
    DataEnd = Cells(Rows.Count, 7).End(xlUp).Row
    'Begin loop to find start and end ranges, create charts based on those ranges
    For i = 1 To DataEnd
        If HasStart Then
            If Cells(i, 4).Value < 0 Then
                EndCell = i
                HasEnd = True
            End If
        Else 'If there isn't a starting cell yet
            If Cells(i, 4).Value > 0.000001 And Cells(i, 5).Value > 0.00000002 Then
                StartCell = i
                HasStart = True
            End If
        End If
        If HasStart And HasEnd Then
            Set ChartRange = ActiveSheet.Range(Cells(StartCell, 7), Cells(EndCell, 7))
            ActiveSheet.Shapes.AddChart(xlLine, _
                                        Left:=ActiveSheet.Range(Cells(StartCell, 10), Cells(StartCell, 10)).Left, _
                                        Top:=ActiveSheet.Range(Cells(StartCell, 10), Cells(StartCell, 10)).Top, _
                                        Width:=ActiveSheet.Range(Cells(StartCell, 10), Cells(StartCell, 20)).Width, _
                                        Height:=ActiveSheet.Range(Cells(StartCell, 10), Cells(StartCell + 25, 10)).Height _
                                        ).Select
            ActiveChart.SetSourceData Source:=ChartRange
            HasStart = False
            HasEnd = False
        End If
    Next
    Application.ScreenUpdating = True
End Sub