I want a code where I have variables P_T1, P_T2 etc, and to be able to loop through them. I read elsewhere that the best way of doing this was to use arrays and I adapted code I found to give me this, but it is not working. Am I way off the mark?
The ranges P_T_table and pt_table are 7 columns wide, looking something like:
<tbody>
</tbody>
Code:
Dim P_T(1 To 4) As Variant
Dim P_TChange(2 To 4) As Date
Dim pt(1 To 4) As Variant
Dim ptChange(2 To 4) As Date
Dim i As Integer
staffrecord = Application.WorksheetFunction.Match(staff_member, staff_names, 0)
sDate = Application.WorksheetFunction.Index(start_dates, staffrecord)
eDate = Application.WorksheetFunction.Index(end_dates, staffrecord)
For i = 1 To 4
P_T(i) = Application.WorksheetFunction.Index(P_T_table, staffrecord, 2i-1)
pt(i) = Application.WorksheetFunction.Index(pt_table, staffrecord, 2i-1)
If i > 1 Then
P_TChange(i) = Application.WorksheetFunction.Index(P_T_table, staffrecord, 2i - 2)
ptChange(i) = Application.WorksheetFunction.Index(pt_table, staffrecord, 2i - 2)
End If
Next iThe ranges P_T_table and pt_table are 7 columns wide, looking something like:
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| pt1 | ptChange2 | pt2 | ptChange3 | pt3 | ptChange4 | pt4 |
<tbody>
</tbody>
