CODEWITHSUNDEEP
vbaexcel
bl3nd3r
bl3nd3r

Reputation: 3

Determining values when looping through multiple ranges in VBA

I am working on a macro to set chart max and mins based on a dynamic data set. When the user chooses their group, the chart updates, and the macro runs to update the chart max and min values to an appropriate scale.

I am hoping someone can help me as I try to use variables from 3 ranges to:

  1. Choose chart based on cell value in range
  2. Set min based on cell value in range
  3. Set max based on cell value in range

At this point I am able to pull out the chart name, but am having trouble getting the value for min and max from the range.

Any help would be appreciated!

Sub rescale()

ActiveSheet.Calculate

Dim ChrtNmRng As Range
Dim ChrtMinRng As Range
Dim ChrtMaxRng As Range
Dim cell As Range

Set ChrtNmRng = Sheets("Data").Range("o5:o20")
Set ChrtMinRng = Sheets("Data").Range("z5:z20")
Set ChrtMaxRng = Sheets("Data").Range("Aa5:Aa20")

For Each cell In ChrtNmRng

With Sheets("Dashboard").ChartObjects(cell.Value).Chart.Axes(xlValue)
    .MinimumScale = ChrtMinRng.Value
    .MaximumScale = ChrtMaxRng.Value
End With

Next cell

End Sub

Upvotes: 0

Views: 415

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

As I understand your comments. Column O contains the name of the charts and the others contain what is wanted as the min and max for each of those charts.

Sub rescale()

ActiveSheet.Calculate

Dim ChrtNmRng As Range
Dim ChrtMinRng As Range
Dim ChrtMaxRng As Range
Dim i As Long

Set ChrtNmRng = Sheets("Data").Range("o5:o20")
Set ChrtMinRng = Sheets("Data").Range("z5:z20")
Set ChrtMaxRng = Sheets("Data").Range("Aa5:Aa20")

For i = 1 To 16

With Sheets("Dashboard").ChartObjects(ChrtNmRng(i).Value).Chart.Axes(xlValue)
    .MinimumScale = ChrtMinRng(i).Value
    .MaximumScale = ChrtMaxRng(i).Value
End With

Next i

End Sub

Upvotes: 1

Alex P
Alex P

Reputation: 12497

Do it like this:

Sub rescale()
    Dim ChrtNmRng As Range, cell As Range

    ActiveSheet.Calculate
    Set ChrtNmRng = Sheets("Data").Range("o5:o20")

    For Each cell In ChrtNmRng
        With Sheets("Dashboard").ChartObjects(cell.Value).Chart.Axes(xlValue)
            .MinimumScale = Range("Z" & cell.Row)
            .MaximumScale = Range("AA" & cell.Row)
        End With
     Next cell  
End Sub

The key here is that your ranges for the minimum and maximum values (Z and AA) line up precisely with column O so you can use the row reference to get the corresponding values you need.

Upvotes: 1

Related Questions