Plot charts with data position unfixed

[catalystx]

Hello, all.

I now want to plot, say, speed vs time using vb in excel.
The problem I have is that, the "speed" data is sometimes located at column O, while it can also be at column P, Q or R depending on the program that generats the file.

Would someone please tell me how to do that?

Appreciate it.

[catalystx]

I tried to use a do...loop to track the column titled "speed" and then get a number "15".

But then I couldn't substitute that number into the following code.

ActiveChart.SetSourceData Source:=Sheets("data").Range("H1:H10928,O1:O10928") _
' , PlotBy:=xlColumns

[Bob Phillips]

Untested

[vba]

colNum = Application.Match("speed",Rows(1),0)
ActiveChart.SetSourceData Source:=Sheets("data").Cells(1,colNum).Resize(10928)
[/vba]

How did you come to 10928?

[catalystx]

Bravo!
When I recorded the macro, I selected the whole column. I guess that's why the number is so big.

[Bob Phillips]

I thought it would be something liek that.

We can improve upon it

[vba]

colNum = Application.Match("speed",Rows(1),0)
rowNum = Cells(Rows.Count,colNum).End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("data").Cells(1,colNum).Resize(rowNum)
[/vba]