Thread: How to Change values by increment in Data Table

Here's the full code. You lost me a bit there so run it and report back on what needs changing.

Sub x()

Dim nMinAP As Long, nMaxAP As Long, nStepAP As Long
Dim nMinAG As Long, nMaxAG As Long, nStepAG As Long
Dim i As Long, nLastcol As Long, nlastrow As Long
Dim rRow, rCol, rRef, j As Long
Dim rStart As Range, rData As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Sheets("Charts").Delete
On Error GoTo 0
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Charts"

Sheets("Mov_Avg_Chart").Activate
nMinAP = Range("B22").Value
nMaxAP = Range("B23").Value
nStepAP = Range("B24").Value
nMinAG = Range("B25").Value
nMaxAG = Range("B26").Value
nStepAG = Range("B27").Value
Set rRow = Range("C8")
Set rCol = Range("C6")
rRef = Array("G7", "G9", "G10", "G11")

nlastrow = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
nLastcol = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If nLastcol < 9 Then
    Range(Cells(15, "I"), Cells(nlastrow, "I")).Clear
Else
    Range(Cells(15, "I"), Cells(nlastrow, nLastcol)).Clear
End If

Set rStart = Range("I15")

For i = LBound(rRef) To UBound(rRef)
    With rStart
        .Formula = "=" & rRef(i)
        .Interior.ColorIndex = 4
        .Offset(1).Value = nMinAP
        .Offset(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=nStepAP, Stop:=nMaxAP
        .Offset(, 1).Value = nMinAG
        .Offset(, 1).DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=nStepAG, Stop:=nMaxAG
        With .CurrentRegion
            .Rows(1).Font.Bold = True
            .Columns(1).Font.Bold = True
        End With
    End With
    Set rData = rStart.Offset(1, 1).CurrentRegion
    With rData
        .NumberFormat = "0.00"
        .Table RowInput:=rRow, ColumnInput:=rCol
        With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
            .Select
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Range("Mov_Avg_Chart!" & .Address)
            ActiveChart.ChartType = xlSurfaceTopView
            ActiveChart.Location xlLocationAsObject, "Charts"
            For j = 1 To rData.Rows.Count - 1
                ActiveChart.SeriesCollection(j).Name = rData(j + 1, 1)
            Next j
            ActiveChart.SeriesCollection(1).XValues = rData(1, 2).Resize(, rData.Columns.Count - 1)
            Sheets("Mov_Avg_Chart").Activate
        End With
    End With
    Set rStart = rStart.Offset(rStart.CurrentRegion.Rows.Count + 1)
Next i

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Hi Stephen,

When I put the new code in, it gave me the error 'Run-time error 1004: Invalid parameter.'

When I hit debug, it highlighted the below portion of code

ActiveChart.SeriesCollection(j).Name = rData(j + 1, 1)

Maani

Are you starting to wonder if we'll ever get there? I think I had the axes the wrong way round - try this:

Sub x()

Dim nMinAP As Long, nMaxAP As Long, nStepAP As Long
Dim nMinAG As Long, nMaxAG As Long, nStepAG As Long
Dim i As Long, nLastcol As Long, nlastrow As Long
Dim rRow, rCol, rRef, j As Long
Dim rStart As Range, rData As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Sheets("Charts").Delete
On Error GoTo 0
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Charts"

Sheets("Mov_Avg_Chart").Activate
nMinAP = Range("B22").Value
nMaxAP = Range("B23").Value
nStepAP = Range("B24").Value
nMinAG = Range("B25").Value
nMaxAG = Range("B26").Value
nStepAG = Range("B27").Value
Set rRow = Range("C8")
Set rCol = Range("C6")
rRef = Array("G7", "G9", "G10", "G11")

nlastrow = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
nLastcol = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If nLastcol < 9 Then
    Range(Cells(15, "I"), Cells(nlastrow, "I")).Clear
Else
    Range(Cells(15, "I"), Cells(nlastrow, nLastcol)).Clear
End If

Set rStart = Range("I15")

For i = LBound(rRef) To UBound(rRef)
    With rStart
        .Formula = "=" & rRef(i)
        .Interior.ColorIndex = 4
        .Offset(1).Value = nMinAP
        .Offset(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=nStepAP, Stop:=nMaxAP
        .Offset(, 1).Value = nMinAG
        .Offset(, 1).DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=nStepAG, Stop:=nMaxAG
        With .CurrentRegion
            .Rows(1).Font.Bold = True
            .Columns(1).Font.Bold = True
        End With
    End With
    Set rData = rStart.Offset(1, 1).CurrentRegion
    With rData
        .NumberFormat = "0.00"
        .Table RowInput:=rRow, ColumnInput:=rCol
        With .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
            .Select
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Range("Mov_Avg_Chart!" & .Address)
            ActiveChart.ChartType = xlSurfaceTopView
            ActiveChart.Location xlLocationAsObject, "Charts"
            For j = 1 To rData.Columns.Count - 1
                ActiveChart.SeriesCollection(j).Name = rStart.Offset(, j)
            Next j
            ActiveChart.SeriesCollection(1).XValues = rStart.Offset(1).Resize(rData.Rows.Count - 1)
            Sheets("Mov_Avg_Chart").Activate
        End With
    End With
    Set rStart = rStart.Offset(rStart.CurrentRegion.Rows.Count + 1)
Next i

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Hi Stephen,

As I was playing around with the spreadsheet this weekend, I kept noticing that whenever the data table values are recalculated in the worksheet, the calculation method changes from 'automatic except data tables' to 'automatic'. Could it possibly be due to

.Calculation = xlCalculationAutomatic

being part of the code? To see this error, try checking the calculation method in the worksheet and then change values in the optimisation table and hit the 'update' button. When you do this, see if you get the workbook recalculating. It would be fine, but the actual workbook I have is very large and this recalculation takes a very long time and slows down the workbook.

Also, as the axes were mixed up, I tried switching the row and column in the code you provided but it gave me a invalid parameter issue.

Could you please advise on how I would switch the axes based on your latest code?

Thanks,

Maani

For calculation, try adding/amending:

ActiveSheet.Calculate 'added
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
       .Calculation = xlCalculationSemiautomatic ' amended
End With

For the axes, it's not clear to me how to achieve what you want - as I say perhaps post in the chart forum where no doubt there will be an expert or two. I can't really visualise how the chart would be different if you switched axes?

The code for the chart was generated by the macro recorder so I'm not completely au fait with it, and as you suggest larger tables do seem to throw up errors. In any case, Andy Pope seems to have sorted it out now so I think everything has been resolved?

Yes, you are correct. Thanks so much with your help with creating a fix; I cannot begin to thank you for all you have done.

Regards,

Maani

Glad that, with Andy's help, we got there.