Excel VBA - find charts name in range and set minumum axes value from range offset(0,1)

Question

Good morning everyone,

I'm trying to do the following thing:

I have a sheet with 4 chart, the names of the graphs are: 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 ideally set the Minumum for every chart.

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

I tried this:

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

Unfortunately it doesn't work, I've tried many other solutions, always worse. I thank so much everyone who can and wants to help me. Have a nice day to all of you, and thank you.

Dario

Answer 1

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