Contour Graphs

Hi Andy,

Thanks for your response and help.

1) If I change the layout as you suggested to leave the top left cell empty, where would I put the statistic that I seek to optimise (the cell that is currently highlighted). Also, how would the code need to be changed? I have tried to play around with this by switching the xlRows and xlColumns in the code but this doesn't work.

2) Thanks for the link on this. Is there a way to automate it? The ranges of the charts and values are always changing and thus I wanted something to be able to automate this by putting it into the macro. Is this possible?

3) When I changed the code to

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

End Sub

the data table doesn't update. That is why it was set to xlCalculationAutomatic. is there a way to have it so that the calculation method remains what it is. That is, if it is set at 'automatic except for data tables' unless i hit the update button and after I hit update it goes back to 'automatic except for data tables'?

Thanks again,

Maani

Just move the topleft cell of the tables over 1 column or up 1.
Anything to help excel use the top row/column as axis labels.

I will try and look at your code tomorrow.

Using the macro recorder setting Calculation except for tables generates this code.

    Application.Calculation = xlSemiautomatic

Thanks for your response, Andy.

1) I kept the top-left cell of each data table empty, but the optimised statistic appears in the cell nonetheless once I hit update. Also, by moving the top-left value to a column up or across, it will affect the macro because it's been set to be a dynamic data table, so it will take values above and to the right of the data table. The reason I did this was so that the data table could adjust to an increase and decrease in values from the optimisation table (cells B22:B27).

3) I also tried to change the code to the one you mentioned below, but the data table didn't update when using this.

Thanks for taking the time to look at the code tomorrow. This has been something of a work in progress for quite awhile now, and as Stephen points out, there has been another thread on this same topic. Thanks to Stephen's help with the code, I have gotten to this point.

Thanks for your time,

Maani

Revised code

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
Dim lngCalcMode As XlCalculation
Dim rngChartOutput As Range

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

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

Set rngChartOutput = Worksheets("Charts").Range("B2:H14")

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("AF7", "AF9", "AF10", "AF11")

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(21, "AE"), Cells(nlastrow, "AE")).Clear
Else
    Range(Cells(21, "AE"), Cells(nlastrow, nLastcol)).Clear
End If

Set rStart = Range("AE21")

For i = LBound(rRef) To UBound(rRef)
    With rStart
        .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
        .Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlSurfaceTopView
        ActiveChart.Location xlLocationAsObject, "Charts"
        With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count)
            .Left = rngChartOutput.Left
            .Top = rngChartOutput.Top
            .Width = rngChartOutput.Width
            .Height = rngChartOutput.Height
        End With
        If i Mod 2 = 0 Then
            ' move to right
            Set rngChartOutput = rngChartOutput.Offset(, rngChartOutput.Columns.Count + 2)
        Else
            ' move down and left
            Set rngChartOutput = rngChartOutput.Offset(rngChartOutput.Rows.Count + 2, -rngChartOutput.Columns.Count - 2)
        End If

        Sheets("Mov_Avg_Chart").Activate
    End With
    With rStart
        .Formula = "=" & rRef(i)
        .Interior.ColorIndex = 17
    End With
    Set rStart = rStart.Offset(rStart.CurrentRegion.Rows.Count + 1)
Next i

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculate
    .Calculation = lngCalcMode
End With

End Sub

For chart titles add these lines to the routine

        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlSurfaceTopView
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = "='" & rStart.Parent.Name & "'!" & rStart.Address(, , xlR1C1)
        ActiveChart.Location xlLocationAsObject, "Charts"

At the start of the routine I store the current calculation setting in a variable, lngCalcMode, just before setting calculation to Manual.

Then at the end I restore calculation mode to the setting.

I did not have to switch the axes as that is the default for that matrix of data. If the matrix dimensions change then you may need to force a plot by rows

activechart.PlotBy=xlRows