Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 Whe=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 Whe=xlLocationAsObject, Name:=ws.Name End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 Whe=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 Whe=xlLocationAsObject, Name:=ws.Name End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Split and graph problem
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem Printing Excel Graph -data doesn't show up when printed | Charts and Charting in Excel | |||
Can you make a "split" or broken y-axis on a graph in Excel? | Charts and Charting in Excel | |||
How do I split the axis scale on a line graph | Excel Discussion (Misc queries) | |||
how do you split the scale (x-axis) on a curve graph? | Charts and Charting in Excel | |||
Split large bar in a graph? | Charts and Charting in Excel |