excel gurus: script to create a scatter of selected block of cells?

[endervalentine]

This might be pretty simple for you excel guys... what I'm trying to figure out is if there is a way to have the script create a scatter plot of the selected cells?

For instance, say we have data from A1:Z100, so the x values for series1 is from A1:Z1, and the y values are from A2:Z2. Likewise for, the x values for series2 is from A3:Z3 and y values for series two are from A4:Z4, so on and so forth.

I can use the macro but the size of the block of data varies every single time ... so it'll have to work for whatever block is selected ... thanks in adv!

 

[TruePaige]

First post..hmm...

I can has free Excel?

 

[Tweak155]

Originally posted by: endervalentine
This might be pretty simple for you excel guys... what I'm trying to figure out is if there is a way to have the script create a scatter plot of the selected cells?

For instance, say we have data from A1:Z100, so the x values for series1 is from A1:Z1, and the y values are from A2:Z2. Likewise for, the x values for series2 is from A3:Z3 and y values for series two are from A4:Z4, so on and so forth.

I can use the macro but the size of the block of data varies every single time ... so it'll have to work for whatever block is selected ... thanks in adv!

Record the macro to get the line of code to create the plot. Then, in the recorded macro where it has the range input, you make it whatever you want.

EDIT:

For your specific list of data, sounds like you can just use the .columns(x) and do something like:

x = 1
while thisworksheet.cells(1, x) <> ""

PLOT.columns(x)
x = x + 1
end

Short and simple.

 

[endervalentine]

Originally posted by: Tweak155

Originally posted by: endervalentine
This might be pretty simple for you excel guys... what I'm trying to figure out is if there is a way to have the script create a scatter plot of the selected cells?

For instance, say we have data from A1:Z100, so the x values for series1 is from A1:Z1, and the y values are from A2:Z2. Likewise for, the x values for series2 is from A3:Z3 and y values for series two are from A4:Z4, so on and so forth.

I can use the macro but the size of the block of data varies every single time ... so it'll have to work for whatever block is selected ... thanks in adv!

Record the macro to get the line of code to create the plot. Then, in the recorded macro where it has the range input, you make it whatever you want.

ok, so excel takes the range that is selected by this -
Range("A1:Z100").Select

How can I break that apart into variables I can use in my for loop? I need to extract out the starting cell, the ending cell in the x direction in this case "Z" as well as the y direction "100" in this case.

for n=1:1:y_size
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E1:GV2")


here is were I put the range of the first series but I'm not sure how I can enter in the variables into the .Range part.