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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.