vba 统计座位关联计数
需求一: 检查所有单元格是否为合并单元格，拆分
流程
1.合并单元格，循环无法遍历 解决方法，检测拆分 If Cells(i, k).MergeCells = True Then ‘Cells(i, k).UnMerge
2.循环判断，跳过循环 Exit For
ElseIf Then
其他参照
展开代码
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
Sub Cal()
Application.ScreenUpdating = False
Dim i, j As Integer
Dim r As Range
'统计数j
j = 0
Set sh1 = Worksheets("3F")
'行数i
For i = 2 To 43
'列数k
For k = 2 To 67
'Set r = Cells(i, 2).MergeArea
'判断是否为合并单元格
If Cells(i, k).MergeCells = True Then
Exit For
'MsgBox ("1")
Else
'如果正常为1则j计数
If Cells(i, k).Value = "1" Then
j = j + 1
End If
End If
Next k
Next i
Cells(46, 2).Value = j
Application.ScreenUpdating = Ture
End Sub
'j为1，h为2，g为3，f为4，d为5,s为9,z为0
需求二: 统计含特定字符的单元格数量
流程
关键在于设置变量以及单元格范围
展开代码
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Sheet1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Sub Cal()
Application.ScreenUpdating = False
Dim i, k, j, h, g, f, d, s, z As Integer
'Dim r As Range
'统计数j为1，h为2，g为3，f为4，d为5,s为9,z为0
i = 0
j = 0
k = 0
h = 0
g = 0
f = 0
d = 0
s = 0
z = 0
n = 53
Set sh1 = Worksheets("2F")
'行数i
For i = 2 To 48
'列数k
For k = 2 To 28
'Set r = Cells(i, 2).MergeArea
'判断是否为合并单元格
If Cells(i, k).MergeCells = True Then
Cells(i, k).UnMerge
'Exit For
'MsgBox ("1")
End If
'Else
'如果正常，j为1，h为2，g为3，f为4，d为5,s为9,z为0
If Cells(i, k).Value = "0" Then
z = z + 1
End If
If Cells(i, k).Value = "1" Then
j = j + 1
ElseIf Cells(i, k).Value = "1+2" Then
j = j + 1
h = h + 1
ElseIf Cells(i, k).Value = "1+3" Then
j = j + 1
g = g + 1
ElseIf Cells(i, k).Value = "1+4" Then
j = j + 1
f = f + 1
ElseIf Cells(i, k).Value = "1+5" Then
j = j + 1
s = s + 1
End If
'j为1，h为2，g为3，f为4，d为5,s为9,z为0
If Cells(i, k).Value = "2" Then
h = h + 1
ElseIf Cells(i, k).Value = "2+3" Then
h = h + 1
g = g + 1
ElseIf Cells(i, k).Value = "2+4" Then
h = h + 1
f = f + 1
ElseIf Cells(i, k).Value = "2+5" Then
h = h + 1
d = d + 1
End If
'j为1，h为2，g为3，f为4，d为5,s为9,z为0
If Cells(i, k).Value = "3" Then
g = g + 1
ElseIf Cells(i, k).Value = "3+4" Then
g = g + 1
f = f + 1
ElseIf Cells(i, k).Value = "3+5" Then
g = g + 1
d = d + 1
End If
If Cells(i, k).Value = "4" Then
f = f + 1
ElseIf Cells(i, k).Value = "4+5" Then
f = f + 1
d = d + 1
End If
If Cells(i, k).Value = "5" Then
d = d + 1
End If
If Cells(i, k).Value = "9" Then
s = s + 1
ElseIf Cells(i, k).Value = "9+2" Then
s = s + 1
h = h + 9
ElseIf Cells(i, k).Value = "9+3" Then
s = s + 1
g = g + 1
ElseIf Cells(i, k).Value = "9+4" Then
s = s + 1
f = f + 1
ElseIf Cells(i, k).Value = "9+5" Then
s = s + 1
d = d + 1
End If
Next k
Next i
Cells(n, 4).Value = z
Cells(n + 1, 4).Value = j
Cells(n + 2, 4).Value = h
Cells(n + 3, 4).Value = g
Cells(n + 4, 4).Value = f
Cells(n + 5, 4).Value = d
Cells(n + 6, 4).Value = s
Application.ScreenUpdating = Ture
End Sub
展开代码
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
Sub Cal()
Application.ScreenUpdating = False
Dim i, k, j, h, g, f, d, s, z, a, b As Integer
'Dim a1 As Integer
'Dim r As Range
'统计数j为1，h为2，g为3，f为4，d为6,s为9,z为0,a8,b7
i = 0 'row
k = 0 'col
n = 53 'write the number
j = 0 'TIO24 gen3 1
h = 0 'left 2
g = 0 'mid up 3
f = 0 'mid down 4
d = 0 'wfh mon 6
s = 0 'another tio24 9
z = 0 'HP 0
a = 0 'dual mon 8
b = 0 'TIO24 gen5 7
Set sh1 = Worksheets("2F")
'行数i
For i = 2 To 49 'TODO
'列数k
For k = 2 To 29 'TODO
'Set r = Cells(i, 2).MergeArea
'判断是否为合并单元格
If Cells(i, k).MergeCells = True Then
Cells(i, k).UnMerge
'Exit For
'MsgBox ("1")
End If
'Else
'如果正常，j为1，h为2，g为3，f为4，d为6,s为9,z为0,a8,b7
If Cells(i, k).Value = "0" Then
z = z + 1
ElseIf Cells(i, k).Value = "0+8" Then
z = z + 1
a = a + 1
ElseIf Cells(i, k).Value = "0+6" Then
z = z + 1
d = d + 1
ElseIf Cells(i, k).Value = "0+7" Then
z = z + 1
b = b + 1
ElseIf Cells(i, k).Value = "0+9" Then
z = z + 1
s = s + 1
ElseIf Cells(i, k).Value = "0+1" Then
z = z + 1
j = j + 1
ElseIf Cells(i, k).Value = "0+2" Then
z = z + 1
h = h + 1
ElseIf Cells(i, k).Value = "0+3" Then
z = z + 1
g = g + 1
ElseIf Cells(i, k).Value = "0+4" Then
z = z + 1
f = f + 1
End If
If Cells(i, k).Value = "1" Then
j = j + 1
ElseIf Cells(i, k).Value = "1+8" Then
j = j + 1
a = a + 1
ElseIf Cells(i, k).Value = "1+6" Then
j = j + 1
d = d + 1
ElseIf Cells(i, k).Value = "1+7" Then
j = j + 1
b = b + 1
ElseIf Cells(i, k).Value = "1+9" Then
j = j + 1
s = s + 1
ElseIf Cells(i, k).Value = "1+0" Then
j = j + 1
z = z + 1
ElseIf Cells(i, k).Value = "1+2" Then
j = j + 1
h = h + 1
ElseIf Cells(i, k).Value = "1+3" Then
j = j + 1
g = g + 1
ElseIf Cells(i, k).Value = "1+4" Then
j = j + 1
f = f + 1
End If
If Cells(i, k).Value = "2" Then
h = h + 1
ElseIf Cells(i, k).Value = "2+8" Then
h = h + 1
a = a + 1
ElseIf Cells(i, k).Value = "2+6" Then
h = h + 1
d = d + 1
ElseIf Cells(i, k).Value = "2+7" Then
h = h + 1
b = b + 1
ElseIf Cells(i, k).Value = "2+9" Then
h = h + 1
s = s + 1
ElseIf Cells(i, k).Value = "2+0" Then
h = h + 1
z = z + 1
ElseIf Cells(i, k).Value = "2+1" Then
h = h + 1
j = j + 1
ElseIf Cells(i, k).Value = "2+3" Then
h = h + 1
g = g + 1
ElseIf Cells(i, k).Value = "2+4" Then
h = h + 1
f = f + 1
End If
If Cells(i, k).Value = "3" Then
g = g + 1
ElseIf Cells(i, k).Value = "3+8" Then
g = g + 1
a = a + 1
ElseIf Cells(i, k).Value = "3+6" Then
g = g + 1
d = d + 1
ElseIf Cells(i, k).Value = "3+7" Then
g = g + 1
b = b + 1
ElseIf Cells(i, k).Value = "3+9" Then
g = g + 1
s = s + 1
ElseIf Cells(i, k).Value = "3+0" Then
g = g + 1
z = z + 1
ElseIf Cells(i, k).Value = "3+1" Then
g = g + 1
j = j + 1
ElseIf Cells(i, k).Value = "3+2" Then
g = g + 1
h = h + 1
ElseIf Cells(i, k).Value = "3+4" Then
g = g + 1
f = f + 1
End If
If Cells(i, k).Value = "4" Then
f = f + 1
ElseIf Cells(i, k).Value = "4+8" Then
f = f + 1
a = a + 1
ElseIf Cells(i, k).Value = "4+6" Then
f = f + 1
d = d + 1
ElseIf Cells(i, k).Value = "4+7" Then
f = f + 1
b = b + 1
ElseIf Cells(i, k).Value = "4+9" Then
f = f + 1
s = s + 1
ElseIf Cells(i, k).Value = "4+0" Then
f = f + 1
z = z + 1
ElseIf Cells(i, k).Value = "4+1" Then
f = f + 1
j = j + 1
ElseIf Cells(i, k).Value = "4+2" Then
f = f + 1
h = h + 1
ElseIf Cells(i, k).Value = "4+3" Then
f = f + 1
g = g + 1
End If
If Cells(i, k).Value = "6" Then
d = d + 1
ElseIf Cells(i, k).Value = "6+8" Then
d = d + 1
a = a + 1
ElseIf Cells(i, k).Value = "6+4" Then
d = d + 1
f = f + 1
ElseIf Cells(i, k).Value = "6+7" Then
d = d + 1
b = b + 1
ElseIf Cells(i, k).Value = "6+9" Then
d = d + 1
s = s + 1
ElseIf Cells(i, k).Value = "6+0" Then
d = d + 1
z = z + 1
ElseIf Cells(i, k).Value = "6+1" Then
d = d + 1
j = j + 1
ElseIf Cells(i, k).Value = "6+2" Then
d = d + 1
h = h + 1
ElseIf Cells(i, k).Value = "6+3" Then
d = d + 1
g = g + 1
End If
If Cells(i, k).Value = "9" Then
s = s + 1
ElseIf Cells(i, k).Value = "9+8" Then
s = s + 1
a = a + 1
ElseIf Cells(i, k).Value = "9+4" Then
s = s + 1
f = f + 1
ElseIf Cells(i, k).Value = "9+7" Then
s = s + 1
b = b + 1
ElseIf Cells(i, k).Value = "9+6" Then
s = s + 1
d = d + 1
ElseIf Cells(i, k).Value = "9+0" Then
s = s + 1
z = z + 1
ElseIf Cells(i, k).Value = "9+1" Then
s = s + 1
j = j + 1
ElseIf Cells(i, k).Value = "9+2" Then
s = s + 1
h = h + 1
ElseIf Cells(i, k).Value = "9+3" Then
s = s + 1
g = g + 1
End If
If Cells(i, k).Value = "8" Then
a = a + 1
ElseIf Cells(i, k).Value = "8+0" Then
a = a + 1
z = z + 1
ElseIf Cells(i, k).Value = "8+4" Then
a = a + 1
f = f + 1
ElseIf Cells(i, k).Value = "8+7" Then
a = a + 1
b = b + 1
ElseIf Cells(i, k).Value = "8+6" Then
a = a + 1
d = d + 1
ElseIf Cells(i, k).Value = "8+9" Then
a = a + 1
s = s + 1
ElseIf Cells(i, k).Value = "8+1" Then
a = a + 1
j = j + 1
ElseIf Cells(i, k).Value = "8+2" Then
a = a + 1
h = h + 1
ElseIf Cells(i, k).Value = "8+3" Then
a = a + 1
g = g + 1
End If
If Cells(i, k).Value = "7" Then
b = b + 1
ElseIf Cells(i, k).Value = "7+0" Then
b = b + 1
z = z + 1
ElseIf Cells(i, k).Value = "7+4" Then
b = b + 1
f = f + 1
ElseIf Cells(i, k).Value = "7+8" Then
b = b + 1
a = a + 1
ElseIf Cells(i, k).Value = "7+6" Then
b = b + 1
d = d + 1
ElseIf Cells(i, k).Value = "7+9" Then
b = b + 1
s = s + 1
ElseIf Cells(i, k).Value = "7+1" Then
b = b + 1
j = j + 1
ElseIf Cells(i, k).Value = "7+2" Then
b = b + 1
h = h + 1
ElseIf Cells(i, k).Value = "7+3" Then
b = b + 1
g = g + 1
End If
Next k
Next i
Cells(n, 4).Value = z '0
Cells(n + 1, 4).Value = j '1
Cells(n + 2, 4).Value = h '2
Cells(n + 3, 4).Value = g '3
Cells(n + 4, 4).Value = f '4
Cells(n + 5, 4).Value = d '6
Cells(n + 6, 4).Value = s '9
Cells(n + 7, 4).Value = a '8
Cells(n + 8, 4).Value = b '7
Application.ScreenUpdating = Ture
End Sub
展开代码
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
Sub Cal()
Application.ScreenUpdating = False
Dim i, k, j, h, g, f, d, s, z, a, b As Integer
'Dim a1 As Integer
'Dim r As Range
'统计数j为1，h为2，g为3，f为4，d为6,s为9,z为0,a8,b7
i = 0 'row
k = 0 'col
n = 53 'write the number
j = 0 'TIO24 gen3 1
h = 0 'left 2
g = 0 'mid up 3
f = 0 'mid down 4
d = 0 'wfh mon 6
s = 0 'another tio24 9
z = 0 'HP 0
a = 0 'dual mon 8
b = 0 'TIO24 gen5 7
Set sh1 = Worksheets("3F")
'行数i
For i = 2 To 43 'TODO
'列数k
For k = 2 To 68 'TODO
'Set r = Cells(i, 2).MergeArea
'判断是否为合并单元格
If Cells(i, k).MergeCells = True Then
Cells(i, k).UnMerge
'Exit For
'MsgBox ("1")
End If
'Else
'如果正常，j为1，h为2，g为3，f为4，d为6,s为9,z为0,a8,b7
If Cells(i, k).Value = "0" Then
z = z + 1
ElseIf Cells(i, k).Value = "0+8" Then
z = z + 1
a = a + 1
ElseIf Cells(i, k).Value = "0+6" Then
z = z + 1
d = d + 1
ElseIf Cells(i, k).Value = "0+7" Then
z = z + 1
b = b + 1
ElseIf Cells(i, k).Value = "0+9" Then
z = z + 1
s = s + 1
ElseIf Cells(i, k).Value = "0+1" Then
z = z + 1
j = j + 1
ElseIf Cells(i, k).Value = "0+2" Then
z = z + 1
h = h + 1
ElseIf Cells(i, k).Value = "0+3" Then
z = z + 1
g = g + 1
ElseIf Cells(i, k).Value = "0+4" Then
z = z + 1
f = f + 1
End If
If Cells(i, k).Value = "1" Then
j = j + 1
ElseIf Cells(i, k).Value = "1+8" Then
j = j + 1
a = a + 1
ElseIf Cells(i, k).Value = "1+6" Then
j = j + 1
d = d + 1
ElseIf Cells(i, k).Value = "1+7" Then
j = j + 1
b = b + 1
ElseIf Cells(i, k).Value = "1+9" Then
j = j + 1
s = s + 1
ElseIf Cells(i, k).Value = "1+0" Then
j = j + 1
z = z + 1
ElseIf Cells(i, k).Value = "1+2" Then
j = j + 1
h = h + 1
ElseIf Cells(i, k).Value = "1+3" Then
j = j + 1
g = g + 1
ElseIf Cells(i, k).Value = "1+4" Then
j = j + 1
f = f + 1
End If
If Cells(i, k).Value = "2" Then
h = h + 1
ElseIf Cells(i, k).Value = "2+8" Then
h = h + 1
a = a + 1
ElseIf Cells(i, k).Value = "2+6" Then
h = h + 1
d = d + 1
ElseIf Cells(i, k).Value = "2+7" Then
h = h + 1
b = b + 1
ElseIf Cells(i, k).Value = "2+9" Then
h = h + 1
s = s + 1
ElseIf Cells(i, k).Value = "2+0" Then
h = h + 1
z = z + 1
ElseIf Cells(i, k).Value = "2+1" Then
h = h + 1
j = j + 1
ElseIf Cells(i, k).Value = "2+3" Then
h = h + 1
g = g + 1
ElseIf Cells(i, k).Value = "2+4" Then
h = h + 1
f = f + 1
End If
If Cells(i, k).Value = "3" Then
g = g + 1
ElseIf Cells(i, k).Value = "3+8" Then
g = g + 1
a = a + 1
ElseIf Cells(i, k).Value = "3+6" Then
g = g + 1
d = d + 1
ElseIf Cells(i, k).Value = "3+7" Then
g = g + 1
b = b + 1
ElseIf Cells(i, k).Value = "3+9" Then
g = g + 1
s = s + 1
ElseIf Cells(i, k).Value = "3+0" Then
g = g + 1
z = z + 1
ElseIf Cells(i, k).Value = "3+1" Then
g = g + 1
j = j + 1
ElseIf Cells(i, k).Value = "3+2" Then
g = g + 1
h = h + 1
ElseIf Cells(i, k).Value = "3+4" Then
g = g + 1
f = f + 1
End If
If Cells(i, k).Value = "4" Then
f = f + 1
ElseIf Cells(i, k).Value = "4+8" Then
f = f + 1
a = a + 1
ElseIf Cells(i, k).Value = "4+6" Then
f = f + 1
d = d + 1
ElseIf Cells(i, k).Value = "4+7" Then
f = f + 1
b = b + 1
ElseIf Cells(i, k).Value = "4+9" Then
f = f + 1
s = s + 1
ElseIf Cells(i, k).Value = "4+0" Then
f = f + 1
z = z + 1
ElseIf Cells(i, k).Value = "4+1" Then
f = f + 1
j = j + 1
ElseIf Cells(i, k).Value = "4+2" Then
f = f + 1
h = h + 1
ElseIf Cells(i, k).Value = "4+3" Then
f = f + 1
g = g + 1
End If
If Cells(i, k).Value = "6" Then
d = d + 1
ElseIf Cells(i, k).Value = "6+8" Then
d = d + 1
a = a + 1
ElseIf Cells(i, k).Value = "6+4" Then
d = d + 1
f = f + 1
ElseIf Cells(i, k).Value = "6+7" Then
d = d + 1
b = b + 1
ElseIf Cells(i, k).Value = "6+9" Then
d = d + 1
s = s + 1
ElseIf Cells(i, k).Value = "6+0" Then
d = d + 1
z = z + 1
ElseIf Cells(i, k).Value = "6+1" Then
d = d + 1
j = j + 1
ElseIf Cells(i, k).Value = "6+2" Then
d = d + 1
h = h + 1
ElseIf Cells(i, k).Value = "6+3" Then
d = d + 1
g = g + 1
End If
If Cells(i, k).Value = "9" Then
s = s + 1
ElseIf Cells(i, k).Value = "9+8" Then
s = s + 1
a = a + 1
ElseIf Cells(i, k).Value = "9+4" Then
s = s + 1
f = f + 1
ElseIf Cells(i, k).Value = "9+7" Then
s = s + 1
b = b + 1
ElseIf Cells(i, k).Value = "9+6" Then
s = s + 1
d = d + 1
ElseIf Cells(i, k).Value = "9+0" Then
s = s + 1
z = z + 1
ElseIf Cells(i, k).Value = "9+1" Then
s = s + 1
j = j + 1
ElseIf Cells(i, k).Value = "9+2" Then
s = s + 1
h = h + 1
ElseIf Cells(i, k).Value = "9+3" Then
s = s + 1
g = g + 1
End If
If Cells(i, k).Value = "8" Then
a = a + 1
ElseIf Cells(i, k).Value = "8+0" Then
a = a + 1
z = z + 1
ElseIf Cells(i, k).Value = "8+4" Then
a = a + 1
f = f + 1
ElseIf Cells(i, k).Value = "8+7" Then
a = a + 1
b = b + 1
ElseIf Cells(i, k).Value = "8+6" Then
a = a + 1
d = d + 1
ElseIf Cells(i, k).Value = "8+9" Then
a = a + 1
s = s + 1
ElseIf Cells(i, k).Value = "8+1" Then
a = a + 1
j = j + 1
ElseIf Cells(i, k).Value = "8+2" Then
a = a + 1
h = h + 1
ElseIf Cells(i, k).Value = "8+3" Then
a = a + 1
g = g + 1
End If
If Cells(i, k).Value = "7" Then
b = b + 1
ElseIf Cells(i, k).Value = "7+0" Then
b = b + 1
z = z + 1
ElseIf Cells(i, k).Value = "7+4" Then
b = b + 1
f = f + 1
ElseIf Cells(i, k).Value = "7+8" Then
b = b + 1
a = a + 1
ElseIf Cells(i, k).Value = "7+6" Then
b = b + 1
d = d + 1
ElseIf Cells(i, k).Value = "7+9" Then
b = b + 1
s = s + 1
ElseIf Cells(i, k).Value = "7+1" Then
b = b + 1
j = j + 1
ElseIf Cells(i, k).Value = "7+2" Then
b = b + 1
h = h + 1
ElseIf Cells(i, k).Value = "7+3" Then
b = b + 1
g = g + 1
End If
Next k
Next i
Cells(n, 4).Value = z '0
Cells(n + 1, 4).Value = j '1
Cells(n + 2, 4).Value = h '2
Cells(n + 3, 4).Value = g '3
Cells(n + 4, 4).Value = f '4
Cells(n + 5, 4).Value = d '6
Cells(n + 6, 4).Value = s '9
Cells(n + 7, 4).Value = a '8
Cells(n + 8, 4).Value = b '7
Application.ScreenUpdating = Ture
End Sub
需求三: 两层映射对应关系
流程
-
根据交换机导出的 mac 地址与端口号，主机名汇总出一份表格，名主机表 Ports Hostname Switch Gi1/0/44 P5R0E CANRSCS01
-
清洗已有端口号与座位端口号的表格，汇总出一份表格，名网口表 Seat Ports 1-01 Gi1/0/3
-
将地图的座位号标志好，名 map 表
-
将主机表的端口和主机名创建字典 1，网口表的座位号和端口创建字典 2，遍历循环判断 map 表单元格是否有座位，可得到对应的端口号和主机名，改写 map 表单元格
展开代码
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109Sub data_match() Application.ScreenUpdating = False Dim dic As Object Dim arr Dim i As Integer Dim k As String Set st2 = Worksheets("sum1") 'g to pc Set st22 = Worksheets("sum2") 'g to pc Set st1 = Worksheets("map") 't Set st3 = Worksheets("s1") 'd to g Set st4 = Worksheets("s2") 'd to g Set dic = CreateObject("scripting.dictionary") ' g to pc 1 Set dic1 = CreateObject("scripting.dictionary") ' g to pc 1 Set dic2 = CreateObject("scripting.dictionary") ' d to g 5 Set dic3 = CreateObject("scripting.dictionary") ' d to g 6 arr = st2.Cells(1, 1).CurrentRegion '总表 For i = 1 To UBound(arr, 1) '这里字典的值，用的是array数组，方便我们一下匹配多个数据，省去再创建字典对象麻烦。 dic(arr(i, 1)) = arr(i, 2) ' Gi1/0/44 P5R0E CANRSCS01 Next arr1 = st22.Cells(1, 1).CurrentRegion '总表 For i = 1 To UBound(arr1, 1) '这里字典的值，用的是array数组，方便我们一下匹配多个数据，省去再创建字典对象麻烦。 dic1(arr1(i, 1)) = arr1(i, 2) ' Gi1/0/44 P5R0E CANRSCS01 Next 'Debug.Print dic("Gi1/0/44") arr2 = st3.Cells(1, 1).CurrentRegion 's1 For i = 1 To UBound(arr2, 1) - 1 '这里字典的值，用的是array数组，方便我们一下匹配多个数据，省去再创建字典对象麻烦。 dic2(arr2(i, 1)) = arr2(i, 2) '551 Gi1/0/44 'Debug.Print TypeName(arr2(i, 1)), TypeName(arr2(i, 2)) Next arr3 = st4.Cells(1, 1).CurrentRegion 's2 For i = 1 To UBound(arr3, 1) - 1 '这里字典的值，用的是array数组，方便我们一下匹配多个数据，省去再创建字典对象麻烦。 dic3(arr3(i, 1)) = arr3(i, 2) 'dic3(Join(Array(arr3(i, 1), arr3(i, 3)), "|")) = arr3(i, 2) '551 Gi1/0/44 ' D|CS = PORT ' "1-01|CANRSCS02" : Variant/String Next 'Debug.Print dic3("1-01|CANRSCS02") 'For Each Key In dic3.items 'Debug.Print Key 'Next 'Debug.Print dic3.Key("Gi1/0/5") 'Debug.Print dic2(551) 'k = dic("Gi1/0/44") 'Debug.Print k For i = 19 To 19 '2 To 43 For j = 63 To 63 '2 To 68 Debug.Print st1.Cells(i, j).Value, TypeName(st1.Cells(i, j).Value) Debug.Print IsNumeric(st1.Cells(i, j).Value) Debug.Print Application.IsNumber(st1.Cells(i, j)) If Application.IsNumber(st1.Cells(i, j)) Then '是否为数字与字符串 'Debug.Print TypeName(st1.Cells(i, j).Value) 'Debug.Print st1.Cells(i, j).Value 'Debug.Print TypeName(dic2(st1.Cells(i, j).Value)) 'Debug.Print dic(dic2(st1.Cells(i, j).Value)) 'Debug.Print TypeName(dic(dic2(Int(st1.Cells(i, j).Value)))) 'Debug.Print dic(dic2(Int(st1.Cells(i, j).Value))) v = st1.Cells(i, j).Value Debug.Print v 'map的D值 - s1的g和s - sum的sn If dic2.exists(v) Then a = dic2(v) b = dic.exists(a) '元素是否存在 If b Then 'Debug.Print "1" st1.Cells(i, j).Value = dic(a) '赋值 'st1.Cells(i, j).Value = dic(dic2(Int(st1.Cells(i, j).Value))) End If End If If dic3.exists(v) Then a = dic3(v) b = dic1.exists(a) If b Then st1.Cells(i, j).Value = dic1(a) '赋值 'Debug.Print st1.Cells(i, j).Value 'st1.Cells(i, j).Value = dic(dic2(Int(st1.Cells(i, j).Value))) End If ' Debug.Print TypeName(b), b 'Debug.Print dic(dic2(Int(st1.Cells(i, j).Value))) 'dic2(int) dic(str) End If End If Next Next Set dic = Nothing Set dic2 = Nothing Application.ScreenUpdating = True End Sub