Excel macro for multiple series in a single scatter graph using 3 columns

[trumptmast]

Hi. I am new to this forum and also new to macros to MS Excel. However, I do have a basic understanding of C++/VB codes and structures. So I really appreciate any help anyone can offer.

I currently am working on writing a macro for Excel to open a text data file and using the the data to make a graph. I already finished writing the part for opening and importing the file. However I am having trouble do the graphing part.

The data is in 3 columns. One is for x-axis (Column 1), one is for y-axis (Column 2) and one is for the IDs (Column 3). There are undefined number of IDs and the number entries under the same the IDs can vary, however they are all grouped together.

For example, the three columns would look like something below (sorry, I don't know how to embed the file in...):

Col1 (x)______Col2 (y)______Col3 (ID)
25.05______218.24______244
349.18______218.82______244
399.66______218.64______244
450.90______217.68______244
800.62______195.84______244
1200.61______160.37______244
24.94______215.09______686
50.91______214.91______686
99.97______214.12______686
150.45______213.16______686
401.91______204.67______686
450.16______202.09______686
800.66______178.10______686
1202.50______146.92______686
24.98______218.56______351
50.43______218.48______351
99.97______218.24______351
149.62______217.97______351

...and so on. There could be as few as one ID that only have one entry in the file or as many as 100 IDs with 100 entries under each of them. The part I cannot figure out is how to graph all the Column 1 vs Column 2 data with the same IDs (same values in Column 3) on the same graph.

Thanks for any help that you guys can offer.

[trumptmast]

Hey, thanks for all those who looked at my post. I have already gotten an answer from another forum.

If anyone is interested, the reply is below:

Andy Pope

Posts: n/a
Re: Excel macro for multiple series in a single scatter graph using3 columns
Hi,

This should get you started.
Assumed your data is in A1:C19, including header row.

Sub CreateChart()

Dim lngRow As Long
Dim lngStartRow As Long
Dim objChart As Chart
Dim objSeries As Series

Set objChart = ActiveSheet.ChartObjects.Add( _
100, 100, 400, 250).Chart
objChart.ChartType = xlXYScatterLines

lngStartRow = 2
lngRow = 2
With ActiveSheet
lngStartRow = 2
lngRow = 2
Do While Len(.Cells(lngRow, 1).Value) > 0
If .Cells(lngStartRow, 3) <> .Cells(lngRow, 3) Then
' deal with grouped ID
Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow, 3).Value
objSeries.XValues = _
..Range("A" & lngStartRow, "A" & lngRow - 1)
objSeries.Values = _
..Range("B" & lngStartRow, "B" & lngRow - 1)
lngStartRow = lngRow
End If
lngRow = lngRow + 1
Loop
Set objSeries = objChart.SeriesCollection.NewSeries
objSeries.Name = .Cells(lngStartRow, 3).Value
objSeries.XValues = .Range("A" & lngStartRow, "A" & lngRow - 1)
objSeries.Values = .Range("B" & lngStartRow, "B" & lngRow - 1)
lngStartRow = lngRow
End With

End Sub

Cheers
Andy

trumptmast wrote:
> ....Can anyone help me with this? Does anyone need more information?
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info