Thread: Adjusting x-axis to run with data range

Hi,

I have built a model that is near completion except for one nagging issue. In the 'BBG Raw Data' tab, if you change the data set or data range, and hit update, it will update the entire model ('data for pivot' tab which feeds the 'mov_avg_chart' tab); thus, if the start and end dates change, I have built a vba function that allows the data to reflect this in the charts ('Mov_Avg_Chart' tab).

If you look at the input table (on 'Mov_Avg_Chart' tab), whenever the colored cells change, the charts update. Cells B3 and C3 reflect the start and end dates for the chart (for the data), cell c4 reflects the units you want reflected in the x-axis in days (i.e. to show x-axis in 30 days, 60 days, etc), and cells c6 and c8 reflect moving average and standard deviation ranges. However, the problem I am having is whenever I change the data set (in 'BBG Raw data' tab and hit update), while the data range in the chart updates, the x-axis does not update to reflect the change in data. Thus, the x-axis still keeps the original range of dates.

I have tried to play around with the code (in the macro entitled 'x-axis_scale') but to no avail. If someone has a chance, could they take a look at the code and see if I am doing something wrong?

Thanks,

Maanihttp://www.4shared.com/file/12145477...odel_2807.html

Am I missing something?

When you press the Update button the routine X_Axis_Scale does not get called. Should it?

The events you have code for are not fired when the Update button is pressed.

Either add a call to the x_axis_scale at the end of the update routine.
Or use code in the Thisworkbook object. Although this will get fired more often.

Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    If Sh.Name = "Mov_Avg_Chart" Then
        X_Axis_Scale
    End If
    
End Sub

Did you try the first suggestion of adding the x_axis_scale call to the update routine?

Then the chart should only update when the button is pressed or relevant cells on worksheet are changed.

This routine is assigned to the Update button on the "BBG Raw Data" worksheet.

So at the end of updating the data also update the chart.

Code:

Sub Dynamic_Table()
    
Dim RowC, Colc As Integer
Dim CRow, CCol As Integer
Dim i As Integer
Dim SupName As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Delete the Data from "Data for Pivot"

With Sheets("Data for Pivot")
    .Range("A3", .Range("A3").End(xlDown)).Clear
    .Range("A3", .Range("A3").End(xlDown)).NumberFormat = "dd/mm/yyyy hh:mm"
End With

' Dynamically count Data in BBG Raw Data sheet

With Sheets("BBG Raw Data")
    .Range("A4", .Range("A4").End(xlDown)).Copy
    Sheets("Data for Pivot").Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End With

' Also update the chart

X_Axis_Scale


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Leave the sheet level event code in for when the coloured cells are manually changed.

Start from the original file you posted.

select the Update button and right click, Edit the assigned macro, which is 'Dynamic_Table'.

Replace that with the code I last posted, which is the same code with the extra line to call the X_axis_scale routine.

place the call after the caluclation has completed.

Code:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

X_Axis_Scale

End Sub

None of the lines start on the 27th. Due to the #N/A the first data point is the 29th.

For that particular chart it is more pronounced as the starting date is further into the axis and the lines are not obscuring each other.

You would need to determine the start point in the data sets and then use that as the start point.