Sub Populate_Response_Times_1_User()
Dim RowStart As Integer
RowStart = 3
'move data from selected column of cells into columns
Call Populate_Response_Times(RowStart)
'style the columns of data
For i = 1 To 5
Call StyleDataColumn(i * 2, RowStart, i Mod 2 = 1)
Next i
End Sub
Sub Populate_Response_Times_25_Users()
Dim RowStart As Integer
RowStart = 26
'move data from selected column of cells into columns
Call Populate_Response_Times(RowStart)
'style the columns of data
For i = 1 To 5
Call StyleDataColumn(i * 2, RowStart, i Mod 2 = 1)
Next i
End Sub
Sub Populate_Response_Times_50_Users()
Dim RowStart As Integer
RowStart = 49
'move data from selected column of cells into columns
Call Populate_Response_Times(RowStart)
'style the columns of data
For i = 1 To 5
Call StyleDataColumn(i * 2, RowStart, i Mod 2 = 1)
Next i
End Sub
Sub Populate_Response_Times(RowStart As Integer)
Dim ColumnIndexMultiplier As Integer
Dim RowIndex As Integer
For i = 0 To Selection.count
If (i Mod 20 = 0) Then
'use next column
ColumnIndexMultiplier = ColumnIndexMultiplier + 1
'restart rows
RowIndex = 0
End If
' select appropriate cell and set it's value to the selected itertor
Cells(RowStart + RowIndex, 2 * ColumnIndexMultiplier).Value = Selection(i + 1)
RowIndex = RowIndex + 1
Next i
End Sub
Sub StyleDataColumn(Column As Integer, Row As Integer, Green As Boolean)
'select column and row range to modify
ActiveSheet.Range(Cells(Row, Column), Cells(Row + 19, Column)).Select
'format look of cells
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Italic = True
If (Green) Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
End If
End Sub
there are three macro's
- Populate_Response_Times_1_User()
- Populate_Response_Times_25_Users()
- Populate_Response_Times_50_Users()
Each one of these leverages the same internal logic it just specifies the insert row to a different value
Home
Page response time for 1 user on test
|
|||||||||||
0 to 5 min
|
5 min to 10 min
|
10 min to 15 min
|
15 min to 20 min
|
20 min to 25 min
|
25 to 30 min
|
||||||
00:15
|
2.06
|
05:15
|
2.10
|
10:15
|
2.13
|
15:15
|
2.06
|
20:15
|
2.08
|
25:15
|
2.32
|
00:30
|
2.19
|
05:30
|
2.06
|
10:30
|
2.17
|
15:30
|
2.27
|
20:30
|
2.16
|
25:30
|
2.27
|
00:45
|
2.11
|
05:45
|
2.03
|
10:45
|
2.10
|
15:45
|
2.10
|
20:45
|
2.31
|
25:45
|
2.36
|
01:00
|
2.12
|
06:00
|
2.28
|
11:00
|
2.09
|
16:00
|
2.03
|
21:00
|
2.07
|
26:00
|
2.61
|
01:15
|
2.04
|
06:15
|
2.08
|
11:15
|
1.90
|
16:15
|
2.13
|
21:15
|
2.21
|
26:15
|
2.29
|
01:30
|
2.08
|
06:30
|
2.05
|
11:30
|
1.99
|
16:30
|
2.06
|
21:30
|
2.34
|
26:30
|
2.26
|
01:45
|
2.03
|
06:45
|
2.40
|
11:45
|
2.44
|
16:45
|
2.18
|
21:45
|
2.61
|
26:45
|
2.61
|
02:00
|
2.09
|
07:00
|
2.22
|
12:00
|
2.13
|
17:00
|
2.15
|
22:00
|
2.00
|
27:00
|
2.28
|
02:15
|
2.07
|
07:15
|
2.08
|
12:15
|
2.14
|
17:15
|
2.09
|
22:15
|
2.30
|
27:15
|
2.65
|
02:30
|
2.00
|
07:30
|
2.11
|
12:30
|
2.23
|
17:30
|
2.29
|
22:30
|
3.81
|
27:30
|
3.19
|
02:45
|
2.06
|
07:45
|
2.28
|
12:45
|
2.16
|
17:45
|
1.99
|
22:45
|
2.50
|
27:45
|
4.53
|
03:00
|
1.97
|
08:00
|
2.19
|
13:00
|
2.11
|
18:00
|
2.04
|
23:00
|
2.08
|
28:00
|
5.86
|
03:15
|
1.98
|
08:15
|
2.28
|
13:15
|
2.12
|
18:15
|
2.05
|
23:15
|
1.97
|
28:15
|
4.86
|
03:30
|
2.07
|
08:30
|
2.20
|
13:30
|
2.14
|
18:30
|
2.06
|
23:30
|
2.25
|
28:30
|
4.36
|
03:45
|
2.01
|
08:45
|
2.13
|
13:45
|
2.12
|
18:45
|
2.28
|
23:45
|
2.82
|
28:45
|
3.87
|
04:00
|
2.02
|
09:00
|
2.30
|
14:00
|
2.23
|
19:00
|
2.05
|
24:00
|
2.41
|
29:00
|
2.19
|
04:15
|
3.14
|
09:15
|
2.09
|
14:15
|
2.27
|
19:15
|
2.15
|
24:15
|
3.63
|
29:15
|
2.13
|
04:30
|
2.16
|
09:30
|
2.29
|
14:30
|
2.15
|
19:30
|
2.05
|
24:30
|
1.97
|
29:30
|
1.96
|
04:45
|
2.25
|
09:45
|
2.12
|
14:45
|
2.05
|
19:45
|
2.21
|
24:45
|
2.23
|
29:45
|
2.54
|
05:00
|
2.15
|
10:00
|
2.13
|
15:00
|
2.04
|
20:00
|
1.92
|
25:00
|
2.22
|
30:00
|
2.42
|
In the excel file we have 3 of the above tables with the 3 rows to start at are 3, 26 and 49.
