2 Axis chart with axis set to xlCategoryScale

[Shaun]

Hi Everyone

I am having some difficulty with a series of charts where the x axis is a date series where not all dates a represented, typically Monday to Friday. This leaves gaps in the charts as the series is date formatted. After I set axis 1 category type to xlCategoryScale to remove those gaps, then select series 4 and assign it to axis group 1 all the data for axis 1 is pushed to the left and data for axis 2 is pushed to the right.

I have guessed at a few things to see if I can fix it, but I don't really understand why it is behaving this way, so I don't really know how to fix or resolve for the other two charts.

I have attached a sample spreadsheet (with code) to demonstrate the issue. Would someone be able to take a look for me please. I have been banging my head on this for sometime now.

Cheers

Shaun

 

[Shaun]

Hi All

Does anyone have any ideas on how correct they way the data displays when I change the axis type to xlCatergoryScale? Or possibly offer a couple of ideas as to what might cause the problem?

Cheers

Shaun

 

[NARAYANK991]

Hi Shaun ,

I changed the axis type to Date , as well as Automatically select based on data , and there is no problem even if you switch to xlCategoryScale.

If the axis type is set to Text , then there is a problem.

Narayan

 

[Shaun]

Hi Narayan

Thank you for your reply.

The problem is the two day gap between the five days of data. I would like to remove the two day gap.

When the data is imported it is being converted to a 41XXX format but displayed as dd/mm/yyyy and I think that Excel is expecting to see consecutive dates. Where dates are missing Excel is including them in the charts with no Y axis values causing the gaps. If I could get Excel to recognise the date column as text I think it will see each date as a n + 1 where in is the previous value.

Perhaps I need to change they way the data is treated on import? I will try that.

Thank you again for your reply.

Cheers

Shaun

 

[Shaun]

Hi Everyone

I thought I would share the resolution to my query for anyone else who may come across this.

Instead of importing Colum A data as a date, I treated as text by changing:

 .TextFileColumnDataTypes = Array(4,1,1,1,1,1)

to

 .TextFileColumnDataTypes = Array(2,1,1,1,1,1)

1 = xlGeneralFormat
2 = xlTextFormat
4 = xlDMYFormat

The additional dates included by Excel are now ignored and the charts now flow nicely.

The change affected the function "OHLC" and "MACD" as it was unable to work with the text. To correct I ' out the parts relating to the x axis and all is well again.

Cheers

Shaun