Help wth arranging/graphing data macro

[frankcase]

Hi,
I have been able to create a marco to organise large quantities my scienfitc data with the grateful assistance of much more able programmers than I.

I need to make the macro a bit more like a template and also make it more flexible to use. Certain parameters change over time (i.e the no of data points Columns Title A, B,C,D,E etc)

I would like the marco when it rearranges the data to automatically highlight all the rearranged data (Columns E2 to M2) until it reaches a blank cell and stop. I would then like the time column (Column D) to fill down until it reaches the end of data rather than manually filling in the cell range in the macro.

After the data is fully arranged I would to draw the x-y graph. At the moment my macro only highlights my selected cell range and then makes the graph.

I am including a excel file of what I am trying to achieve.
Thanks,
Frank

 

[XL Guru]

Hi Frank. I won't get too involved with your request because I only do "kludge" code and you already have some "proper" code ; having a mish-mash of methods might not be a good idea. I'll give you a couple of pointers tho'.

1. Your code was in the module for Sheet1, this is wrong. I've moved it to a new module.

2. "I would like the time column to fill down until the end of data rather than manually filling in the cell range in the macro".

OK. I've added a small macro ("Lines") that you can use instead of the "Add Time Title" section:

Sub Lines()
Range("D1") = "Time, Seconds"
Range("D2") = 1
Range("D2").Resize(Range("E65536").End(xlUp).Row - 1).DataSeries _
Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End Sub

The first 2 lines (green) should be self-explanatory. The rest uses the End(xlUp).Row method to figure the last used cell in column E and thus how far to fill down column D (NB: the data needs to be in column E already for it to be able to figure the last used cell, of course).

HTH,
Andy

 

[frankcase]

Hi Andy,

The "ines" marco is just what I need to achieve my goal but with one problem. The time data is a variable in my studies. I would like Cell D2 to be variable and D3 is a forumula =D3+D$2. It would be 1second and the following cell would be D3+1sec. That is why I used the formula. Then your marco would automatically fill down the forumla to the end of the data set.

Finally, I would like to automatically graph the entire set of rearranged data using a simple x-y plot with time on the x axis. My method requires manually changing the data range but I am afraid that I could have thousands of data points which makes a very large excel file, 6Mb in size and makes my macro more cumersome to use.

I recorded a macro of manually graphing the data. So if this macro could automatically know the start and end of the data and then plot the chart in a x-y plot with time on the x axis. That would be my final goal for this marco
Sub Macro9()
'
' Macro9 Macro
'
Range("D1:N4811").Select
Range("N4811").Activate
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"CE-ICP-MS Data"
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("D1:N4811")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
End Sub

I know my coding is probalby not best method of solving this problem but it has advanced my aim of dealing with large quantities of data from our instrument.
Thanks for the help so far.
Frank.

 

[XL Guru]

>> I would like D2 to be variable and D3 is a formula =D3+D$2

So when would variable D2 be defined? Maybe something like:

Sub Lines()
Range("D1") = "Time, Seconds"
Range("D2") = Application.InputBox("Enter D2 value.")
Range("D3").Resize(Range("E65536").End(xlUp).Row - 2).FormulaR1C1 = "=R[-1]C+R2C"
End Sub

>> automatically know the start and end of the data

You might be able to wangle this with a dynamic named range:

Insert -- Name -- Define
Name = ChData
Refers to:
=OFFSET(dataorganisemacro!$D$1,0,0,COUNTA(dataorganisemacro!$D:$D),COUNTA(dataorganisemacro!$1:$1))

& then a macro like:

Sub MakeChart()
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("dataorganisemacro").Range("ChData") _
, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="dataorganisemacro"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "CE-ICP-MS Data"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

This could probably do with some cleaning up but I'm quite pushed ; try & check in later.

HTH,
Andy

 

[XL Guru]

Here is what I've done (note that my code methods are not recommended, probably).

There's a macro in the attached, "Organise". It (a) does the transposing and fixes up column D (b) runs another macro "MakeChart".

With your data there are 10 "selections", A -- J. The user is prompted to provide this info, viz:

x = Application.InputBox("Number of selections (A -- ?)")

The transposing is done by a loop. The loop duration = count of values in column B divided by x (7, in your case).

The problem with a "macro of manually graphing the data" is it gives you a fixed range for the source, e.g. Source:=Sheets("Sheet2").Range("D1:N4811")

Using a dynamic named range:

(Source:=Sheets("dataorganisemacro").Range("ChData")

worked for me. You can check out the definition of ChData via Insert -- Name -- Define.

HTH,
Andy