Using VBA in Excel 2010 to create Charts

[Warda]

HI

I am working on huge amount of Data and I need to create charts for more than 277 sheets each with over 10000 rows and 92 Columns.

The Data are measurmnets for some force over the year and these measurments are recorded in a table for 365 days and for each day there are 24 records a record for ecah hour.

Some titles of the Columns are Titeld as Following : MM=Month , DD=Day , HH=Hour , Force=WG.

What I want is a VBA code to create a chart with HH on x-axis and WG on Y-axis I tried a code from this forum it did work but the plots are overlapping so on x-axis it is showing just from 0-24 and the ploting. I wnat the x-axis to be continous 0-24 then again 0-24 and so on.

Please if you can help me with that?

The table below shows a sample of what I mean.


[TABLE="width: 320"]
<tbody>[TR]
[TD] MM[/TD]
[TD] DD[/TD]
[TD] HH[/TD]
[TD] WG[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6,1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9,7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10,8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9,7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9,7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8,7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10,8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]7,7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]6,6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]7,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]5,6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]5,6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]5,6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]7,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]7,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9,2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9,7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]12,3[/TD]
[/TR]
</tbody>[/TABLE]

the code I did use is
Private Sub CommandButton3_Click()


Application.ScreenUpdating = False
Dim LastRow As Long
Dim xTitle As Range
Dim xData As Range
Dim yColumn As Long
Dim yTitle As Range
Dim yData As Range
Dim GraphRange As Range


'Find last row with Data
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.count, "A").End(xlUp).Row


'Set x-axis title and Data
Set xTitle = Range("C1")
Set xData = Range("C2:C" & LastRow)


'Find Temperature, set y-axis title
With Rows(1)
Set yTitle = .Find("WG", LookIn:=xlValues)
yColumn = yTitle.Column
End With


'set y-axis data
Set yData = Range(Cells(2, yColumn), Cells(LastRow, yColumn))


'set total graph range
Set GraphRange = Union(xTitle, xData, yTitle, yData)


'create chart
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=GraphRange
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.SetElement (msoElementLegendNone)
ActiveChart.ChartTitle.Text = "Chart Title Here"
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = xTitle
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = yTitle

Application.ScreenUpdating = True


End Sub

 

[oldbrewer]

[TABLE="width: 449"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]xaxis as values[/TD]
[TD]yaxis[/TD]
[TD][/TD]
[TD]xaxis as text[/TD]
[TD]yaxis[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]by converting the x axis values to text[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]I made a bar chart where the x axis was[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]1,3,5,1,3,5,1,3,5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Warda]

Hi oldbrewer

Do you mean I need to change cells form to text for the whole table or just for the x-axis valuse I need?

I tried it but it didn't work.

thanks in advanced

 

[oldbrewer]

as my post says, I converted x axis labels to text, the Y values have to be values, my bar chart was fine.

do what i did make a new spreadsheet with simple dummy data to test ogut the methodology........

 

[oldbrewer]

you have 6,1 etc change to 6.1

 

[oldbrewer]

[TABLE="width: 838"]
<colgroup><col><col><col><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]number of characters[/TD]
[TD]position of comma[/TD]
[TD]characters before comma[/TD]
[TD]characters after comma[/TD]
[TD]pre decimal point number[/TD]
[TD]post decimal point number[/TD]
[TD]actual decimal number[/TD]
[TD]final number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17,2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]17[/TD]
[TD]2[/TD]
[TD]0.2[/TD]
[TD]17.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4,567[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]567[/TD]
[TD]0.567[/TD]
[TD]4.567[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6543454,54547[/TD]
[TD]13[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]6543454[/TD]
[TD]54547[/TD]
[TD]0.54547[/TD]
[TD]6543454.545[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 9"]using LEN, SEARCH, LEFT and MID the text number is converted to a proper number to enable it to be charted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]