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

joecrabtree wrote:
>
> 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

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

On Dec 12, 2:42 pm, Nik <n...@completely.invalid> wrote:
> joecrabtree wrote:
>
> > 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

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

On Dec 12, 2:42 pm, Nik <n...@completely.invalid> wrote:
> joecrabtree wrote:
>
> > 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

joecrabtree wrote:
> 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?
>

Sorry - tested OK for me.

Working example at www.niksally.f2s.com/temp/datasplit.xls

Nik

joecrabtree wrote:
> 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

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


Nik wrote:
> joecrabtree wrote:
> > 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

joecrabtree wrote:
> 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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Nik" <(E-Mail Removed)> wrote in message
news:elohal$9k4$(E-Mail Removed)...
> joecrabtree wrote:
>> 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