Adding Data to a range

[scottscott]

I am trying to write a macro which will plot data from a text file. The data which i am trying to plot is in column B and it contains both x&y values:
B

x1
y1
x2
y2

The number of data points will always be different. I currently know the starting and ending points, but i am having trouble creating the the x and y ranges. Here is my first attempt where l1 is the starting row and l2 is the ending row:

Set x1 = Worksheets(ws).Range("B" & l1)
Set y1 = Worksheets(ws).Range("B" & l1 + 1)

For i = l1 + 2 To l2 - 1 Step 2
Set x1 = Union(x1, Worksheets(ws).Range("B" & i))
Worksheets(ws).Cells(6, 1).Value = x1
Set y1 = Union(y1, Worksheets(ws).Range("B" & i + 1))
Next i

I am having trouble adding a single element to a range. Is it possible to add a single element to an range? If so how would i accomplish this task? Is there a better method for plotting these graphs?

 

[SkipVought]

Hi,

Please post some sample data. Are the pairs always in adjacent cells?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scottscott]

Hello Skip,

Yeah the pairs unfortunately are always in adjacent cells.

time
thickness
time
thickness

Trying to plot thickness versus time.

 

[scottscott]

One more try

 

[scottscott]

Ok, new question how do i post attachments?

 

[SkipVought]

Just copy 'n' paste the data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scottscott]

For now i just post to plot the first column of data

ProcessTime
Thickness

Timer: Layer 1 0:00:15 0:00:15 0:00:00
Output 2: CuPC 0 0.03 -99.1 1.1
Timer: Layer 1 0:00:30 0:00:30 0:00:00
Output 2: CuPC 0.0002 0 -100 2.6
Timer: Layer 1 0:00:45 0:00:45 0:00:00
Output 2: CuPC 0.0014 0.03 -99.1 3
Timer: Layer 1 0:01:00 0:01:00 0:00:00
Output 2: CuPC 0.03 0 -100 3

 

[SkipVought]

So the data that you posted is ALL in one column?

How would you PARSE the data into separate columns?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scottscott]

I all ready have the data parsed into separate columns. The file is actually tab delimited and is imported very cleanly into excel.

 

[SkipVought]

Let me say it another way, then.

Would you please parse the data for me, or line it up column for column, so that I know what data goes in what column? Please use TGML markup, [tt]. If you don't know what that is, then SEARCH on this page for TGML.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scottscott]

Process: SMOLED Run: 310 Date: 7/15/2008 Time: 10:50:41

Event: Layer, ProcessTime, LayerTime, DepositTime
Output: Film, Thickness, Rate, Dev, Power


Timer: Layer 1, 0:00:15, 0:00:15, 0:00:00
Output 2: CuPC, 0.0000, 0.03, -99.1, 1.1
Timer: Layer 1, 0:00:30, 0:00:30, 0:00:00
Output 2: CuPC, 0.0000, 0.00, -100.0, 2.6
Timer: Layer 1, 0:00:45, 0:00:45, 0:00:00
Output 2: CuPC, 0.0000, 0.03, -99.1, 3.0
Timer: Layer 1, 0:01:00, 0:01:00, 0:00:00
Output 2: CuPC, 0.0000, 0.00, -100.0, 3.0
Timer: Layer 1, 0:01:16, 0:01:16, 0:00:00
Output 2: CuPC, 0.0000, 0.00, -100.0, 3.0
Timer: Layer 1, 0:01:31, 0:01:31, 0:00:00
Output 2: CuPC, 0.0000, -0.03, -100.9, 3.0
Timer: Layer 1, 0:01:46, 0:01:46, 0:00:00
Output 2: CuPC, 0.0000, 0.03, -99.1, 5.1
Timer: Layer 1, 0:02:01, 0:02:01, 0:00:00
Output 2: CuPC, 0.0002, 0.03, -99.1, 8.5
Timer: Layer 1, 0:02:16, 0:02:16, 0:00:00
Output 2: CuPC, 0.0008, 0.08, -97.4, 9.0
Timer: Layer 1, 0:02:32, 0:02:32, 0:00:00
Output 2: CuPC, 0.0035, 0.08, -97.4, 9.0
Timer: Layer 1, 0:02:47, 0:02:47, 0:00:00
Output 2: CuPC, 0.0048, 0.10, -96.6, 9.1
Timer: Layer 1, 0:03:02, 0:03:02, 0:00:00
Output 2: CuPC, 0.0065, 0.13, -95.7, 9.2
Timer: Layer 1, 0:03:17, 0:03:17, 0:00:00
Output 2: CuPC, 0.0099, 0.31, -89.8, 9.2
Timer: Layer 1, 0:03:32, 0:03:32, 0:00:00
Output 2: CuPC, 0.0168, 0.59, -80.4, 9.2
Timer: Layer 1, 0:03:48, 0:03:48, 0:00:00
Output 2: CuPC, 0.0282, 0.89, -70.2, 9.2
Timer: Layer 1, 0:04:03, 0:04:03, 0:00:00
Output 2: CuPC, 0.0449, 1.28, -57.5, 9.2
Timer: Layer 1, 0:04:18, 0:04:18, 0:00:00
Output 2: CuPC, 0.0674, 1.66, -44.7, 9.2
Timer: Layer 1, 0:04:33, 0:04:33, 0:00:00
Output 2: CuPC, 0.0952, 1.96, -34.5, 9.2
Timer: Layer 1, 0:04:49, 0:04:49, 0:00:00
Output 2: CuPC, 0.1276, 2.30, -23.5, 9.2
Timer: Layer 1, 0:05:04, 0:05:04, 0:00:00
Output 2: CuPC, 0.1644, 2.55, -15.0, 9.2
Timer: Layer 1, 0:05:19, 0:05:19, 0:00:00
Output 2: CuPC, 0.2053, 2.93, -2.2, 9.2
End PreCon: Layer 1, 0:05:21, 0:05:21, 0:00:00
Output 2: CuPC, 0.0015, 2.98, -0.5, 9.2
End ShtrDelay: Layer 1, 0:05:21, 0:05:21, 0:00:00
Output 2: CuPC, 0.0000, 2.98, -0.5, 9.2
Timer: Layer 1, 0:05:34, 0:05:34, 0:00:13
Output 2: CuPC, 0.0386, 3.03, 1.2, 9.1
Timer: Layer 1, 0:05:49, 0:05:49, 0:00:28
Output 2: CuPC, 0.0827, 3.01, 0.3, 9.2
Timer: Layer 1, 0:06:05, 0:06:05, 0:00:43
Output 2: CuPC, 0.1297, 3.09, 2.9, 9.0
Timer: Layer 1, 0:06:20, 0:06:20, 0:00:58
Output 2: CuPC, 0.1745, 2.93, -2.2, 9.1
Timer: Layer 1, 0:06:35, 0:06:35, 0:01:14
Output 2: CuPC, 0.2195, 3.11, 3.7, 9.0
Timer: Layer 1, 0:06:50, 0:06:50, 0:01:29
Output 2: CuPC, 0.2649, 2.96, -1.4, 9.0
End Deposit: Layer 1, 0:07:03, 0:07:03, 0:01:41
Output 2: CuPC, 0.3022, 3.01, 0.3, 9.0
End Deposit: Layer 1, 0:07:03, 0:07:03, 0:01:41
Output 2: CuPC, 0.3022, 3.01, 0.3, 9.0
End Feed: Layer 1, 0:07:03, 0:07:03, 0:01:41
Output 2: CuPC, 0.3022, 3.01, 0.3, 9.0

 

[SkipVought]

So, using the data that you just posted, what values are you trying to plot? Just the values in column B, which are x,y pares?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scottscott]

Ultimately, I would like to plot values from both columns B and C, but i should be able to plot C once we figure out how to plot B. x,y pares for column B are as follows:

Timer: Layer 1, x1, 0:00:15, 0:00:00
Output 2: CuPC, y1, 0.03, -99.1, 1.1
Timer: Layer 1, x2, 0:00:30, 0:00:00
Output 2: CuPC, y2, 0.00, -100.0, 2.6
Timer: Layer 1, x3, 0:00:45, 0:00:00
Output 2: CuPC, y3, 0.03, -99.1, 3.0

So the first row of data contains the process time and the second row of data contains the measured thickness.

 

[SkipVought]

I'd write a pgm to put all the data in a proper table that you can easily plot from. ONE ROW for all related data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scottscott]

I was afraid of that.
Thanks Skip!

 

[SkipVought]

You can use MS Query rather than a program, to correct the data. faq68-5829

First, delete the second row of headings data and the empty rows.

Then add this formula in the last column with a Heading of PAIR...
[tt]
=MOD(ROWS(),2)
[/tt]
assuming that your HEADINGS are in Row 1 and your DATA begins in Row 2.
Here's the query, assuming that your data is on Sheet1
[tt]
SELECT
A.Pair
, A.`ProcessTime`
, A.`LayerTime`
, A.`DepositTime`
, CDbl(B.`ProcessTime`) As Thickness
, CDbl(B.`LayerTime`) As Rate
, CDbl(B.`DepositTime`) As Dev
, CDbl(B.F5) As Power

FROM
`Sheet1$` A
, `Sheet1$` B

WHERE A.`Event: Layer`='Timer: Layer 1'
AND B.`Event: Layer`=' Output 2: CuPC'
And A.Pair = B.Pair
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}