Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need some help with visual basic to use cell ref for Y axis values

    Hello and thanks in advance for your help. Novice with VBA...I have a worksheet which contains 11 charts and I would like to use cell references to set the Y axis values, both mix and max. I have those min and max values in cells AA5:AK6. Here is what I have written so far and I am not having any luck:

    Sub yaxisrecord()
    '
    ' yaxisrecord Macro
    '

    '

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$AA$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$AA$5").Value

    ActiveSheet.ChartObjects("Chart4").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$AB$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$AB$5").Value

    ActiveSheet.ChartObjects("Chart6").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ac$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ac$5").Value

    ActiveSheet.ChartObjects("Chart7").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ad$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ad$5").Value

    ActiveSheet.ChartObjects("Chart11").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ae$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ae$5").Value

    ActiveSheet.ChartObjects("Chart9").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Af$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Af$5").Value

    ActiveSheet.ChartObjects("Chart10").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ag$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ag$5").Value

    ActiveSheet.ChartObjects("Chart13").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ah$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ah$5").Value

    ActiveSheet.ChartObjects("Chart12").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ai$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ai$5").Value

    ActiveSheet.ChartObjects("Chart14").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Aj$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Aj$5").Value

    ActiveSheet.ChartObjects("Chart15").Activate
    ActiveChart.Axes(xlCategory).MaximumScale = ActiveSheet.Range("$Ak$6").Value
    ActiveChart.Axes(xlCategory).MinimumScale = ActiveSheet.Range("$Ak$5").Value





    End Sub

    What do i need to correct?

  2. #2
    New Lounger
    Join Date
    Sep 2012
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    change to code posted above...

    the xlCategory text has been changed to xlValue...still not able to debug past the first section of code

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Perhaps (untested):
    Code:
    Sub SetYaxisScales()
        Dim dblMax                As Double
        Dim dblMin                As Double
        Dim vCharts
        Dim vItem
        
        dblMax = ActiveSheet.Range("$AA$6").Value
        dblMin = ActiveSheet.Range("$AA$5").Value
    
    
        vCharts = Array("Chart 1", "Chart4", "Chart6", "Chart7", "Chart9", "Chart10", "Chart11", "Chart12", "Chart13", "Chart14", "Chart15")
        For Each vItem In vCharts
            With ActiveSheet.ChartObjects(vItem).Chart.Axes(xlValue)
                .MaximumScale = dblMax
                .MinimumScale = dblMin
            End With
        Next vItem
    
    
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •