PDA

View Full Version : Excel - how can I fill a column with determinated values?



frade
06-24-2005, 01:22 AM
Hello,

Please have a look at this sheet 'intro'. I would like to modify
the code of this sheet to fill automatically the column D of the second
sheet (this column is called 'reference sample' and contains
the values already defined in 'intro' (column E)

The number of references depends on the number of samples
(you have well a reference for each sample)
In my example, you have 4 samples and references
but the number of references can be lower of higher...

What could I do?

Thanks a lot for your help

Regards,

Fran?ois

xld
06-24-2005, 03:44 AM
Try this


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long, l As Long
Dim nRows As Long, nRows2 As Long
Dim Test2

On Error GoTo ws_exit
Application.EnableEvents = False

With Worksheets("Repeatability")

If Target.Address = "$B$5" Then
.Range("A2:D2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents

Test = Target.Value
Days = Me.Range("B3").Value
Repetitions = Range("b4").Value
l = 0: c = 0
For i = 1 To Days
For k = 1 To Test
For j = 1 To Repetitions
.Range("c2").Offset(l, 0) = k
.Range("c2").Offset(l, -1) = i
.Range("c2").Offset(l, -2) = l + 1
.Range("c2").Offset(l, 2) = j
l = l + 1
Next j
Next k
Next i
End If

Me.Range("D2").Resize(Cells(Rows.Count, "D").End(xlUp).Row).ClearContents
Test2 = Target.Value
s = 0
For n = 1 To Test2
Me.Range("D2").Offset(s, 0) = n
s = s + 1
Next n

nRows2 = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("D2").Formula = "=VLOOKUP(C2,Intro!$D:$E,2,False)"
.Range("D2").AutoFill .Range("D2").Resize(nRows2 - 1)

.Range("A1") = "ID"
.Range("B1") = "DAY"
.Range("C1") = "NUMBER OF SAMPLES"
.Range("D1") = "REF_SAMPLE"
.Range("E1") = "NUM OF REPEAT"
.Range("F1") = "RESULTS"
.Range("G1") = "AVERAGE BY DAY"
.Range("H1") = "AVERAGE BY REP"
.Range("I1") = "SQUARE:(Ei-Gi)^2"
.Range("J1") = "SUM OF SQUARES_BY_DAY"
.Range("K1") = "SUM OF SQUARES_DIV_SAMPLES"
.Range("L1") = "STANDARD DEVIATION BY DAY"
.Range("M1") = "TOT_SUM_OF_SQUARES"
.Range("N1") = "TOT_SUM_OF_SQUARES_DIV_SAMPLES*NB_DAYS"
.Range("O1") = "STANDARD DEVIATION BETWEEN DAY"
.Range("P1") = "AVERAGE BETWEEN DAY"
.Range("Q1") = "CV_BY_DAY"
.Range("R1") = "CV_BETWEEN_DAY"
End With

ws_exit:
Application.EnableEvents = True
End Sub



and mine is a case of Duval :)

frade
06-24-2005, 05:31 AM
Many thanks for your help :friends:

I have a little remark about the colum D of the sheet 'intro'
This column whose the header is "num of sample" must depends only on the cell A5
(Number of samples) and not depends on others values (Number of days, Number of repetitions,..). Indeed, if i change one of theses values, automatically the column D
change and it's not the goal in this case..

Generally speaking, the values will be entered in this order

First step: enter the references of the samples (column E)
Second step : enter the number of days + enter the number of repetitions
Third step : enter the number of samples then, by pressing enter, the columns D
(sheet 'intro'+sheet 'repeatability') are automatically filled.

What could I change?

Thanks a lot

Fran?ois

xld
06-25-2005, 03:14 AM
I have a little remark about the colum D of the sheet 'intro'
This column whose the header is "num of sample" must depends only on the cell A5
(Number of samples) and not depends on others values (Number of days, Number of repetitions,..). Indeed, if i change one of theses values, automatically the column D
change and it's not the goal in this case..

Generally speaking, the values will be entered in this order

First step: enter the references of the samples (column E)
Second step : enter the number of days + enter the number of repetitions
Third step : enter the number of samples then, by pressing enter, the columns D
(sheet 'intro'+sheet 'repeatability') are automatically filled.

What could I change?

Thanks a lot

Fran?ois

Untested, but I think you want



Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long, l As Long
Dim nRows As Long, nRows2 As Long
Dim Test2

On Error GoTo ws_exit
Application.EnableEvents = False

With Worksheets("Repeatability")

If Not Intersect(Target, Range("B3:B5,E:E")) Is Nothing Then
.Range("A2:D2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents

Test = Me.Range("B5").Value
Days = Me.Range("B3").Value
Repetitions = Range("b4").Value
l = 0: c = 0
For i = 1 To Days
For k = 1 To Test
For j = 1 To Repetitions
.Range("c2").Offset(l, 0) = k
.Range("c2").Offset(l, -1) = i
.Range("c2").Offset(l, -2) = l + 1
.Range("c2").Offset(l, 2) = j
l = l + 1
Next j
Next k
Next i
End If

Me.Range("D2").Resize(Cells(Rows.Count, "D").End(xlUp).Row).ClearContents
Test2 = Me.Range("B5").Value
s = 0
For n = 1 To Test2
Me.Range("D2").Offset(s, 0) = n
s = s + 1
Next n

nRows2 = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("D2").Formula = "=VLOOKUP(C2,Intro!$D:$E,2,False)"
.Range("D2").AutoFill .Range("D2").Resize(nRows2 - 1)

.Range("A1") = "ID"
.Range("B1") = "DAY"
.Range("C1") = "NUMBER OF SAMPLES"
.Range("D1") = "REF_SAMPLE"
.Range("E1") = "NUM OF REPEAT"
.Range("F1") = "RESULTS"
.Range("G1") = "AVERAGE BY DAY"
.Range("H1") = "AVERAGE BY REP"
.Range("I1") = "SQUARE:(Ei-Gi)^2"
.Range("J1") = "SUM OF SQUARES_BY_DAY"
.Range("K1") = "SUM OF SQUARES_DIV_SAMPLES"
.Range("L1") = "STANDARD DEVIATION BY DAY"
.Range("M1") = "TOT_SUM_OF_SQUARES"
.Range("N1") = "TOT_SUM_OF_SQUARES_DIV_SAMPLES*NB_DAYS"
.Range("O1") = "STANDARD DEVIATION BETWEEN DAY"
.Range("P1") = "AVERAGE BETWEEN DAY"
.Range("Q1") = "CV_BY_DAY"
.Range("R1") = "CV_BETWEEN_DAY"
End With

ws_exit:
Application.EnableEvents = True
End Sub

frade
06-28-2005, 02:44 PM
Thanks a lot xld for your help! :hi:

Regards,

Fran?ois