W
Wfc
Hi,
I need help in writing a Macro that can format data that has been
brought in to the spreadsheet using winwedge pro and a DDE.
I have only been working with macros for the last week and so far hav
just only been able to get the input with the DDE working.
I am using Excel 2000.
To give the short and sweet version of what i am trying to do is
i have data in a column like this
500
525
562
525
500
end
753
753
951
852
122
end
852
852
159
854
856
end
Basically 3 sets of data seperated by "end".
the lengths of the 3 sets are always the same as each other as th
first value in each group makes up a test sample, the second value i
each group makes the second value etc.
But the lengths of the sets can be up to 2500 values each, so a ful
test would be 7500 values. there is no way of determining the legnth o
the tests before i get the results.
what i need to do is line up the 3 sets of data in to columns
so it will look like this
row1 row2 row3
500 753 852
525 753 852
562 951 159
525 852 854
500 122 856
i have tried recoding a simple macro but i dont know how to make i
take in to account that the test sizes will always be different. i ha
it search for "end" and then copy everything below that line and past
it into the top of the next and then repeat the procedure in the nex
column.
any help with this would be greatly appreciated
Thanks
Warren
Below is the long winded version of what i am tring to do
To give background information i have a micro controller feeding out
data on to a serial port and then i use win wedge to put the data in t
excel using the DDE in a macro.
For each test the data puts out 3 sets of data. 1 displacement array
and 2 acceleration arrays(from 2 seperate accelerometers).
what happens it that for every change in displacement the
microcontroller records the displacement a reading from accerometer 1
and a reading from accelerometer 2.
once the test is finished either by reaching 2500 displacement values
or being timed out the micro controller will output the data on to th
serial port.
it will come out in to excel like this
each new line in a new row,
100
98
98
65
23
end
600
500
600
655
400
end
600
528
655
455
322
end
now this test is just a short one of 5 displacement values, then 5
acceleration values from accelerometer 1 and 5 acceleration values for
accelerometer 2. the first displacement value corresponds to the firs
acceleration value from each accel array and so-forward.
Now the issue i have is that the size of these tests are always
different and can be anywhere up to 2500 displacement values long
(making the whole data column 7500 values long.
what i need to make is a macro that after the DDE has placed the data
in the spreadsheet it will find the first "end" and cut from that cel
down to the bottom of the data series and paste it in the next column
and then delete the "end" to allign the data points. Then it will nee
to perform the operation again in this second column to make a 3rd
column.
once it has performed that i need to the top of the 4th column to wai
for a next set of data to do it all again.
so the end result would have the data looking like this
100 600 600
98 500 528
98 600 655
65 655 455
23 400 322
these 3 data arrays will always be the same length as each other in th
single test, but the test sizes can change.
Eg. test 1 above has 5 points, but test 2 may have 2500 points.
i have tried recording a macro to do this however i am unsure what
steps to take to make it always cut from the first "end" down while
taking in to account different test sizes as the macro only cuts and
pastes from referencing cell positions
I need help in writing a Macro that can format data that has been
brought in to the spreadsheet using winwedge pro and a DDE.
I have only been working with macros for the last week and so far hav
just only been able to get the input with the DDE working.
I am using Excel 2000.
To give the short and sweet version of what i am trying to do is
i have data in a column like this
500
525
562
525
500
end
753
753
951
852
122
end
852
852
159
854
856
end
Basically 3 sets of data seperated by "end".
the lengths of the 3 sets are always the same as each other as th
first value in each group makes up a test sample, the second value i
each group makes the second value etc.
But the lengths of the sets can be up to 2500 values each, so a ful
test would be 7500 values. there is no way of determining the legnth o
the tests before i get the results.
what i need to do is line up the 3 sets of data in to columns
so it will look like this
row1 row2 row3
500 753 852
525 753 852
562 951 159
525 852 854
500 122 856
i have tried recoding a simple macro but i dont know how to make i
take in to account that the test sizes will always be different. i ha
it search for "end" and then copy everything below that line and past
it into the top of the next and then repeat the procedure in the nex
column.
any help with this would be greatly appreciated
Thanks
Warren
Below is the long winded version of what i am tring to do
To give background information i have a micro controller feeding out
data on to a serial port and then i use win wedge to put the data in t
excel using the DDE in a macro.
For each test the data puts out 3 sets of data. 1 displacement array
and 2 acceleration arrays(from 2 seperate accelerometers).
what happens it that for every change in displacement the
microcontroller records the displacement a reading from accerometer 1
and a reading from accelerometer 2.
once the test is finished either by reaching 2500 displacement values
or being timed out the micro controller will output the data on to th
serial port.
it will come out in to excel like this
each new line in a new row,
100
98
98
65
23
end
600
500
600
655
400
end
600
528
655
455
322
end
now this test is just a short one of 5 displacement values, then 5
acceleration values from accelerometer 1 and 5 acceleration values for
accelerometer 2. the first displacement value corresponds to the firs
acceleration value from each accel array and so-forward.
Now the issue i have is that the size of these tests are always
different and can be anywhere up to 2500 displacement values long
(making the whole data column 7500 values long.
what i need to make is a macro that after the DDE has placed the data
in the spreadsheet it will find the first "end" and cut from that cel
down to the bottom of the data series and paste it in the next column
and then delete the "end" to allign the data points. Then it will nee
to perform the operation again in this second column to make a 3rd
column.
once it has performed that i need to the top of the 4th column to wai
for a next set of data to do it all again.
so the end result would have the data looking like this
100 600 600
98 500 528
98 600 655
65 655 455
23 400 322
these 3 data arrays will always be the same length as each other in th
single test, but the test sizes can change.
Eg. test 1 above has 5 points, but test 2 may have 2500 points.
i have tried recording a macro to do this however i am unsure what
steps to take to make it always cut from the first "end" down while
taking in to account different test sizes as the macro only cuts and
pastes from referencing cell positions