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

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.