Excel - how can I fill a column with determinated values?

[frade]

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

[Bob Phillips]

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]

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

[Bob Phillips]

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]

Thanks a lot xld for your help! :hi:

Regards,

Fran?ois