XY scatter, dates and VBA

[dourpil]

Hi everyone and good day!

How can I use my dates as the X axis in an XY scatter chart with vba?

So far, the only way I managed to have my dates in my chart is by selecting my dates, "Data" > "Text to columns" > Finish.
I don't know if that can be done in vba (but I hope so) !


What I do as of now:

1. I'm getting a date from a userform:
data.Cells(nr, 1) = Format(CDate(Me.TbDate), "dd/mm/yyyy")

2. Moving that cell to another location (with a (useless) line to try formatting):
temp.Cells(xtemp, y).NumberFormat = "dd/mm/yyyy"
temp.Cells(xtemp, y) = data.Cells(x, 1)

3. Setting a range that contains my dates:
Set rdates = temp.Range(Cells(2, y), Cells(xtemp - 1, y))

4. [This step comes from a thread I've seen when looking for a solution. Someone suggested to redim the dates as long]:

ReDim longdates(rdates.Count) As Long
For j = 1 To rdates.Count
longdates(j) = rdates(j)
Next j

5. Adding chart and series
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.ChartType = xlXYScatter
.Axes(xlCategory).TickLabels.NumberFormat = "dd/mm/yyyy"
With .SeriesCollection.NewSeries
.Values = rserie1
.XValues = longdates
end with
end with


This plots all points on the Y axis (X = 0).


Any thoughts?


___

EDIT: I had a problem I didn't notice earlier. My redimming of dates as longdates is actually not working. ATM I have a type mismatch error. Investigating.. Still open to suggestions to get my dates right


EDIT2: I tried recording a TextToColumn macro to use in my code, but the output isn't the same. Nothing changes when I run the macro although manual method works.

 

[bobhc]

Edit three post moved to correct section of the forum

 

[dourpil]

Edit four thanks for your reply bob although since my issues were also about cell formatting and about charting, it could have gone into many sections of the forum.

Solved it using the amazingly simple (but so damn hard to find) DateValue(...) function which converts a date stored as text into a date stored as a number. Seen a lot of threads on the matter, only one provided insightful information. Phew!

Now my code looks like this:

1. Same
2. temp.Cells(xtemp, y).NumberFormat = "dd/mm/yyyy"
temp.Cells(xtemp, y) = DateValue(data.Cells(x, 1))

3. same although I now use rdates as .XValues
4. Deleted
5. Same