Data Split and graph problem

J

joecrabtree

To all,

I have a set of data in two columns A: A, and B:B( Sheet name DATA ).
The A column contains times, and the B column contains temperatures:
i.e:


13:14:39 949
13:15:39 949
13:16:39 949
13:17:39 949
13:18:39 949
13:19:39 949
13:20:39 949
13:21:39 949
13:22:39 949
13:23:39 949


12:53:26 819
12:54:26 819
12:55:26 819
12:56:26 819
12:57:26 819
12:58:26 819
12:59:26 819
13:00:26 819
13:01:26 819
13:02:26 819
13:03:26 819
13:04:26 819

What I want to do is split the data if there is a difference between
two adjacent times of greater than 10 minutes. So for example where I
have put a line break in the data - this is where it would be split. I
then want each 'split' set of data to be put in a new worksheet, and a
chart created for each set of data. The number of splits can vary for
each set of data.

Is this possible?

Thanks to all in advance,

Kind Regards

Joseph Crabtree
 
N

Nik

joecrabtree said:
What I want to do is split the data if there is a difference between
two adjacent times of greater than 10 minutes. So for example where I
have put a line break in the data - this is where it would be split. I
then want each 'split' set of data to be put in a new worksheet, and a
chart created for each set of data. The number of splits can vary for
each set of data.

The following will split the data as you've described.

Where do you wan t the charts to go, and what sort of chart do you want?

I've assumed no title rows on the data.

HTH, Nik
-------------------

Sub NikTest()
Dim MyRow As Integer
Dim a1Cell As Range
Set a1Cell = Sheet1.Range("a1")

allrows = Sheet1.UsedRange.Rows.Count
For MyRow = allrows - 1 To 1 Step -1
'Working upwards - always seems a good idea when inserting rows.

If a1Cell.Offset(MyRow, 0) - a1Cell.Offset(MyRow - 1, 0) > (1 /
144) Then
'We have a >10 minute gap between myrow and the row above it.

a1Cell.Offset(MyRow, 0).EntireRow.Insert
a1Cell.Offset(MyRow + 1, 0).CurrentRegion.Copy
'End With

With sheets.Add

.Paste Destination:=Range("a1")


End With

End If
Next

a1Cell.CurrentRegion.Copy

With sheets.Add
.Paste Destination:=Range("a1")
End With
End Sub
 
J

joecrabtree

Hi

Thats great, it splits the data, i.e puts a new row in between each
set. It then creates the new worksheets where it will paste the data,
however the worksheets remain blank, as it doesn't paste the data to
them. Any Ideas?

Thanks

Joseph Crabtree
 
J

joecrabtree

Regarding the chart type - I then wanted to create a Line chart with
time on the x axis, and temperature on the y axis for each set of data
( Each worksheet ). This chart would then be displayed in its own
worksheet, rather then being embedded into an existing worksheet.

Your help is much apprceciated.

Thanks

Joseph Crabtree
 
N

Nik

joecrabtree said:
Regarding the chart type - I then wanted to create a Line chart with
time on the x axis, and temperature on the y axis for each set of data
( Each worksheet ). This chart would then be displayed in its own
worksheet, rather then being embedded into an existing worksheet.
This does the graphs, but I think that to get a proper time-based axis
you will have to have a scatter plot rather than a line graph. I'm not
an expert on XL charts, so I may be wrong.

Nik


Sub NikTest()
Dim MyRow As Integer
Dim a1Cell As Range
Set a1Cell = Sheet1.Range("a1")

allrows = Sheet1.UsedRange.Rows.Count
For MyRow = allrows - 1 To 1 Step -1
'Working upwards - always seems a good idea when inserting rows.

If a1Cell.Offset(MyRow, 0) - a1Cell.Offset(MyRow - 1, 0) > (1 /
144) Then
'We have a >10 minute gap between myrow and the row above it.

a1Cell.Offset(MyRow, 0).EntireRow.Insert
a1Cell.Offset(MyRow + 1, 0).CurrentRegion.Copy

Set ws = Sheets.Add

ws.Paste Destination:=Range("a1")

With Charts.Add
.ChartType = xlXYScatter
.SetSourceData Source:=ws.Range("A1").CurrentRegion, PlotBy _
:=xlColumns
.Location Where:=xlLocationAsObject, Name:=ws.Name

End With

End If
Next

a1Cell.CurrentRegion.Copy
Set ws = Sheets.Add

ws.Paste Destination:=Range("a1")

With Charts.Add
.ChartType = xlXYScatter
.SetSourceData Source:=ws.Range("A1").CurrentRegion, PlotBy _
:=xlColumns
.Location Where:=xlLocationAsObject, Name:=ws.Name

End With

End Sub
 
J

joecrabtree

Hi,


Just had a thought, I placed this macro in the worksheet and ran it
from a button rather than in a module. Could this be the reason it
didn't work?

Thanks

Joseph
 
N

Nik

joecrabtree said:
Hi,


Just had a thought, I placed this macro in the worksheet and ran it
from a button rather than in a module. Could this be the reason it
didn't work?

I can't see why it makes a difference, but it seems to. The button is
okay, but the code being in a worksheet seems to produce the probelm you
describe. The 'ThisWorkbook' object is okay...

Any ideas why, anyone?

Nik
 
J

Jon Peltier

The procedure should go into a regular code module, not a worksheet module
or the ThisWorkbook module.

If the button is a controls toolbox button, call the main procedure from the
_Click event procedure in the worksheet code module. If it's a forms toolbar
button, assign the main procedure to the button.

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top