VBA - Modification of SourceData (Dynamic Range) of existing STock OHLC Chart

Question

I am working on Stock OHLC Chart present in Sheets("Exhibit") and selecting Data from Sheets("75Min"). However i am succeeding to choose appropriate data range but can not add at Line Nu 15 .SetSourcedata, could you please help me to get out from this problem Code is as follows.

Sub Edit75MinChartToOHLCCandlestickChart()

Dim OHLCChart As ChartObject

Dim LastRow As Integer

Dim RngSt As Integer

    Sheets("75Min").Select

    Range("A1").Select

    Range("A1").End(xlDown).Select

    LastRow = ActiveCell.Row

    RngSt = LastRow - 59

    RngEnd = LastRow + 15

    Set OHLCChart = ThisWorkbook.Worksheets("Exhibit").ChartObjects(1)

        With OHLCChart.Chart 'Worksheets("Exhibit").ChartObjects("Chart 2").Chart

            .SetSourceData ThisWorkbook.Worksheets("75Min").Range(RngSt, RngEnd)

            .ChartType = xlStockOHLC

            .HasTitle = True

            .ChartTitle.Text = "75Min Candlestick chart"

            .Axes(xlValue, xlPrimary).HasTitle = False

            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"

            .PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 242, 242)

            .ChartArea.Format.Line.Visible = msoFalse

            .Parent.Name = "OHLC Chart"

        End With

    End Sub

Thank You

Answer 1

Best to avoid Select and Activate. Your range is not a valid range. You are saying Range(#,#), which isn't valid and I doubt you were trying to get whole rows. Assuming you only wanted the first column, this will work. You can expand the column from 1 if you need the labels.

Sub Edit75MinChartToOHLCCandlestickChart()

    Dim OHLCChart As ChartObject

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Set ws1 = ThisWorkbook.Worksheets("75Min")
    Set ws2 = ThisWorkbook.Worksheets("Exhibit")
    Dim MyRng As Range
    Dim LastRow As Long
    Dim RngSt As Long
    Dim RngEnd as Long
    LastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

    RngSt = LastRow - 59

    RngEnd = LastRow + 15
    
    Set MyRng = ws1.Range(ws1.Cells(RngSt, 1), ws1.Cells(RngEnd, 1))
    
    Set OHLCChart = ws2.ChartObjects(1)

        With OHLCChart.Chart 'Worksheets("Exhibit").ChartObjects("Chart 2").Chart

            .SetSourceData MyRng

Answer 2

You can include these lines to adjust the Y axis scale:

Dim Ymin As Double, Ymax As Double
Ymin = WorksheetFunction.Min(MyRng)
Ymax = WorksheetFunction.Max(MyRng)

With OHLCChart.Chart.Axes(xlValue)
  .MinimumScale = Ymin
  .MaximumScale = Ymax
End With

But these are probably going to be ugly axis limits. I've written a tutorial called Calculate Nice Axis Scales in Excel VBA which contains an algorithm to adjust the min and max outwards slightly so that the scale is more human-readable.