excelvbacharts

Find chart name in range and set minimum axes value from range offset(0,1)


I have a sheet with four charts: CHT_1, CHT_2, CHT_3, CHT_4.

In range ("u6;u9") I have the name of chart.
In range("v6:v9") I have a value to set the Minimum for every chart.

I would like to find the name of chart in range("u6:u9") and set the minimum for this (every) chart for value found in range("v6:v9").

Sub SET_MIN_CHART()
    Application.ScreenUpdating = FALSE
    
    Dim MyRange As Range
    
    Dim sh As Worksheet, i As Long
    Set sh = Worksheets("Foglio1")
    Dim cht As Chart
    Set MyRange = Range("u6:W9")
    
    If sh.ChartObjects.Count > 0 Then
        
        For i = 1 To sh.ChartObjects.Count
            Set cht = sh.ChartObjects(i).Chart
            cht.Axes(xlValue).MinimumScale = MyRange.Cells(i, 1).Value
        Next i
    End If
    
    Application.ScreenUpdating = TRUE
End Sub

Solution

  • I think you are close.

    Your loop should only run on the range holding the names of the chart. You can then access the value for the minimum of the axes from the cell next to the cell holding the chart name, you can use the Offset-function for that.

    You can access the charts of a sheet either by index or by name. In your case, using the name comes handy.

    Your code could look like this:

    Sub SetMinChart()
        Dim cell As Range
        With ThisWorkbook.Sheets("Foglio1")
            For Each cell In .Range("U6:U9")
                Dim chartName As String, minValue As Double
                chartName = cell.Value
                minValue = cell.Offset(0, 1).Value
                
                Dim co As ChartObject
                Set co = Nothing
                On Error Resume Next
                Set co = .ChartObjects(chartName)
                On Error GoTo 0
                If co Is Nothing Then
                    MsgBox "Chart " & chartName & " not found."
                Else
                    co.Chart.Axes(xlValue).MinimumScale = minValue
                End If
            Next cell
        End With
    End Sub