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:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]pt1[/TD]
[TD="align: center"]ptChange2[/TD]
[TD="align: center"]pt2[/TD]
[TD="align: center"]ptChange3[/TD]
[TD="align: center"]pt3[/TD]
[TD="align: center"]ptChange4[/TD]
[TD="align: center"]pt4[/TD]
[/TR]
</tbody>[/TABLE]
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 i
The ranges P_T_table and pt_table are 7 columns wide, looking something like:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]pt1[/TD]
[TD="align: center"]ptChange2[/TD]
[TD="align: center"]pt2[/TD]
[TD="align: center"]ptChange3[/TD]
[TD="align: center"]pt3[/TD]
[TD="align: center"]ptChange4[/TD]
[TD="align: center"]pt4[/TD]
[/TR]
</tbody>[/TABLE]