PDA

View Full Version : Plot charts with data position unfixed



catalystx
12-02-2007, 05:15 PM
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. :beerchug:

catalystx
12-02-2007, 05:17 PM
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
12-02-2007, 05:49 PM
Untested



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


How did you come to 10928?

catalystx
12-02-2007, 06:04 PM
Bravo!
When I recorded the macro, I selected the whole column. I guess that's why the number is so big.

Bob Phillips
12-03-2007, 01:23 AM
I thought it would be something liek that.

We can improve upon it



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)