[SOLVED] Adjusting x-axis to run with data range

maani · #1 07-29-2009, 06:37 AM

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

Andy Pope · #2 07-29-2009, 06:53 AM

Am I missing something?

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

maani · #3 07-29-2009, 07:04 AM

Hello Andy,

The update button simply updates the data and dumps it into the 'data for pivot' tab. The charts are updated based upon the data that is in the 'data for pivot' tab. Whenever any of the colored inputs (in 'Mov_Avg_Chart' tab) are changed, the x_axis_scale routine should get called and the charts change. Thus, if you put in a new data set or data range, and hit update, the date goes into the 'data for pivot' tab and thus cells b3 in Mov_Avg_Chart tab (start date) and c3 in Mov_Avg_Chart tab(end date) change to reflect the new data range. Also, if you change any of the other inputs manually (c6 or c8 in 'Mov_Avg_Chart' tab), the charts are changed and the various macros are called.

Maani

Andy Pope · #4 07-29-2009, 07:33 AM

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

maani · #5 07-29-2009, 08:05 AM

Thanks Andy. When I do this, the issue I am getting is that when I change the data set in 'BBG Raw Data' tab, it changes the charts automatically without hitting the update button. That is, if you change the data set or change the range of it in the 'BBG Raw Data' tab, and don't hit update, go back and look at the 'Mov_Avg_Chart' tab and you notice the charts have changed. How the spreadsheet was previously set up, if you change the data in 'BBG Raw Data', there would only be a change made if you hit the update button. However, if you change one of the colored cells in the 'Mov_Avg_Chart' tab, the charts would automatically update without hitting the update button. Would it be possible to have the chart work in the same way?

Thanks,

Maani

Andy Pope · #6 07-29-2009, 08:35 AM

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.

maani · #7 07-29-2009, 08:50 AM

I'm not sure what you mean by adding the x_axis_scale call to the update routine. I tried adding it to the end of the macro named 'x_axis_scale' and at the end of the macro named 'update it', and in both circumstances the chart updates without hitting the update button (that is, the same as the 'this workbook'object). Where exactly should I be adding the code you provided? Could you let me know after which series of code? Sorry for my confusion.

Andy Pope · #8 07-29-2009, 08:54 AM

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

maani · #9 07-29-2009, 09:06 AM

I tried to put in the code you mentioned in your latest post and it still updates when I don't hit the update button. I also tried adding the code from your 2nd post after the 'Update It' macro and it updates without hitting the update button. Could you please advise the exact code I should be putting in and where?

Andy Pope · #10 07-29-2009, 11:54 AM

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.

maani · #11 07-29-2009, 12:44 PM

Thanks Andy. When I do this, the data updates but not the x-axis unless I hit update twice. For some reason the x-axis won't update after hitting update once. I have to hit update once, wait for the data to populate, then hit update again. Would there be a way to do this so that I just hit update once and the x-axis updates?

Thanks for being patient with me

Maani

Andy Pope · #12 07-30-2009, 04:25 AM

place the call after the caluclation has completed.

Code:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

X_Axis_Scale

End Sub

maani · #13 07-30-2009, 04:55 AM

Hi Andy,

Thanks. That seems to work. I just noticed that on the 'Cumulative P&L vs. Drawdown' Chart ('Mov_Avg Chart' tab, 2nd chart on right side) tab, the x-axis doesn't begin where the data begins. That is, the x-axis begins on 27/07/2008 while the first data point is 04/08/2008. In this chart, the data range and data set is different than the other charts. Is there a way to have the x-axis begin on this chart where the data set begins?

Maani

Andy Pope · #14 07-30-2009, 05:22 AM

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.

maani · #15 07-30-2009, 05:27 AM

Ok thanks. I really appreciate your help.

Best,

Maani

Welcome to the Excel Forum

Forum Statistics