-
Notifications
You must be signed in to change notification settings - Fork 11
/
Excel - Tennis_Database_Codes.vb
68 lines (65 loc) · 1.89 KB
/
Excel - Tennis_Database_Codes.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
Private Function get_tournament_key(ByVal tour As String) As Variant
'
' This function returns the tournament's key
' by searching the tour table Query
'
Dim wk As Worksheet
Set wk = Worksheets("Tournaments")
Dim t As Range
Set t = wk.Range("tour_table")
Dim q(3) As Variant
On Error Resume Next
r = t.Find(tour, searchorder:=xlRows, MatchCase:=True).Row
'Use minus one to get the upper row
q(0) = t(r - 1, 1)
q(1) = t(r - 1, 3)
q(2) = t(r - 1, 6)
q(3) = t(r - 1, 5)
tour_key = q(0)
If IsEmpty(tour_key) Then
get_tournament_key = ""
Else
get_tournament_key = q
End If
End Function
Sub complete_tournament_sheet()
'
' Use this sub to get the key of a given
' tournament by searching the tour table
'
Dim wk As Worksheet
Set wk = Worksheets("MainTour")
Dim tournament_range As Range
Set tournament_range = wk.Range(wk.Range("C1").Offset(1, 0).Address, wk.Range("C1").End(xlDown).Address)
range_count = tournament_range.Count
Dim q_array As Variant
Dim v, h As Long
v = 1
h = 0
'
' The for-each loop is used in order to cycle through
' all the range
'
For Each tour In tournament_range
q_array = get_tournament_key(tour)
For Each w In q_array
'
' A second for loop is used to cycle through the
' array that was sent back with the tournament
' carachteristics
'
wk.Range("B1").Offset(v, h).Value = w
'We move to the next column
h = h + 1
'We skip the second column for the third
If h = 1 Then
h = h + 1
End If
Next
'We move to the next row
v = v + 1
'We reset the column letter to come back
'to the first column of the row
h = 0
Next
End Sub