View Full Version : Sleeper: VBA - sort data
frade
05-09-2005, 08:25 AM
Hello,
Please have a look at my file.
In sheet3, when you click on the button "sort data" placed
(for information in the column O), the data are sorted by "num of repeat"
then by day and number of dilutions..Ok the first step works well! but...after... when I click on the button "REPRODUCIBILITY" , the results haved changed
(you can follow that by seeing the link between ID and result of this ID)
What can I change in my code?
Thanks a lot!
Fran?ois
HI
Is the data in sheet3 supposed to be related to sheet2? Is it supposed to be a raw copy (at least columns A:E)?
If so, then how did the result in sheet2, cell F2 get calculated? IF it is the same formula used in the macro from the reproducibility button, then it may pay to check. When I put that formula into sheet2, I got a different answer for the average of item 1 (113 cf 8.5).
Tony
frade
05-09-2005, 09:56 PM
Hi,
Thanks for your help. Yes, sheet 2 and sheet 3 are well related...the first five columns are the same(same results also).
I would like automatically to create a exact copy of this columns
directly in sheet 3. But what could I do?
Fran?ois
PS: The colum F (sheet2) is the average for the results with the same day and dilution
Hi
So you want to create a value copy of columns A:F from Sheet2 to sheet3? Do you want it to copy on the press of a button, or when something changes on sheet 2? What is the trigger that will bring the data from sheet2 to sheet3?
Tony
frade
05-09-2005, 11:46 PM
Hi,
Thanks Tony. I would like to create a copy
when something changes on sheet 2 (just when
something has changed on the five first columns)
Franks
frade
05-10-2005, 01:15 AM
Do you have an idea?
Thanks
Fran?ois
Hi
You could put a change event macro on sheet 2. Right click on the sheet2 tab, select view code and enter
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <= 5 Then
lastrow = Sheets("sheet2").Range("a65536").End(xlUp).Row
Sheets("sheet3").Range("a1:e" & lastrow) = Sheets("sheet2").Range("a1:e" & lastrow).Value
End If
End Sub
There are a couple of assumptions in this.
1) The last row in sheet2 will cover any previous data in sheet3
2) Column A will always have data going to the last row.
If you are likely to have data in sheet3 that will be longer than existing data in sheet2, then it may pay to do a test of the last rows in both sheets, and take whichever is the larger value.
Tony
frade
05-11-2005, 02:03 AM
Ok acw, thanks for your advice.
In this case, I prefer to keep my code to avoid mistakes
when I change the results in sheet2
fran?ois
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.