teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問VBA 行数だけ繰り返す

編集履歴

質問編集履歴

11

誤記

2020/05/28 10:01

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -28,87 +28,179 @@
28
28
 
29
29
  Private Sub CommandButton3_Click()
30
30
 
31
+ ActiveCell.FormulaR1C1 = "みかん"
32
+
33
+ Sheets("Sheet1").Select
34
+
35
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=1, Criteria1:="みかん"
36
+
31
- Sheets("Data").Select
37
+ Sheets("Data").Select
38
+
39
+ Range("B2").Select
40
+
41
+ ActiveCell.FormulaR1C1 = "S"
42
+
43
+ Sheets("Sheet1").Select
44
+
45
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=2, Criteria1:="S"
46
+
47
+ Sheets("Data").Select
48
+
49
+ Range("C2").Select
50
+
51
+ ActiveCell.FormulaR1C1 = "A"
52
+
53
+ Sheets("Sheet1").Select
54
+
55
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=3, Criteria1:="A"
56
+
57
+ Sheets("Data").Select
58
+
59
+ Range("D2").Select
60
+
61
+ ActiveCell.FormulaR1C1 = "120"
62
+
63
+ Sheets("Sheet1").Select
64
+
65
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=4
66
+
67
+ Sheets("Data").Select
68
+
69
+ Range("E2").Select
70
+
71
+ ActiveCell.FormulaR1C1 = "10"
72
+
73
+ Sheets("Sheet1").Select
74
+
75
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=5, Criteria1:="10"
76
+
77
+ Sheets("Data").Select
78
+
79
+ Range("F2").Select
80
+
81
+ ActiveCell.FormulaR1C1 = "1200"
82
+
83
+ Sheets("Sheet1").Select
84
+
85
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=6
86
+
87
+ Range("A2:AB185").Select
88
+
89
+ Selection.Copy
90
+
91
+ Sheets("Sheet2").Select
32
92
 
33
93
  Range("A2").Select
34
94
 
35
- ActiveCell.FormulaR1C1 = "みかん"
36
-
37
- Sheets("Sheet1").Select
38
-
39
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=1, Criteria1:="みかん"
40
-
41
- Sheets("Data").Select
42
-
43
- Range("B2").Select
44
-
45
- ActiveCell.FormulaR1C1 = "S"
46
-
47
- Sheets("Sheet1").Select
48
-
49
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=2, Criteria1:="S"
50
-
51
- Sheets("Data").Select
52
-
53
- Range("C2").Select
54
-
55
- ActiveCell.FormulaR1C1 = "A"
56
-
57
- Sheets("Sheet1").Select
58
-
59
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=3, Criteria1:="A"
60
-
61
- Sheets("Data").Select
62
-
63
- Range("D2").Select
64
-
65
- ActiveCell.FormulaR1C1 = "120"
66
-
67
- Sheets("Sheet1").Select
68
-
69
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=4
70
-
71
- Sheets("Data").Select
72
-
73
- Range("E2").Select
74
-
75
- ActiveCell.FormulaR1C1 = "10"
76
-
77
- Sheets("Sheet1").Select
78
-
79
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=5, Criteria1:="10"
80
-
81
- Sheets("Data").Select
82
-
83
- Range("F2").Select
84
-
85
- ActiveCell.FormulaR1C1 = "1200"
86
-
87
- Sheets("Sheet1").Select
88
-
89
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=6
90
-
91
- Range("A2:AB14").Select
95
+ ActiveSheet.Paste
96
+
97
+ Sheets("Data").Select
98
+
99
+ Range("A3").Select
100
+
101
+ Application.CutCopyMode = False
102
+
103
+ ActiveCell.FormulaR1C1 = "りんご"
104
+
105
+ Sheets("Sheet1").Select
106
+
107
+ ActiveWindow.ScrollColumn = 11
108
+
109
+ ActiveWindow.ScrollColumn = 10
110
+
111
+ ActiveWindow.ScrollColumn = 9
112
+
113
+ ActiveWindow.ScrollColumn = 8
114
+
115
+ ActiveWindow.ScrollColumn = 7
116
+
117
+ ActiveWindow.ScrollColumn = 6
118
+
119
+ ActiveWindow.ScrollColumn = 5
120
+
121
+ ActiveWindow.ScrollColumn = 4
122
+
123
+ ActiveWindow.ScrollColumn = 3
124
+
125
+ ActiveWindow.ScrollColumn = 2
126
+
127
+ ActiveWindow.ScrollColumn = 1
128
+
129
+ Rows("1:1").Select
130
+
131
+ Selection.AutoFilter
132
+
133
+ Selection.AutoFilter
134
+
135
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=1, Criteria1:="りんご"
136
+
137
+ Sheets("Data").Select
138
+
139
+ Range("B3").Select
140
+
141
+ ActiveCell.FormulaR1C1 = "M"
142
+
143
+ Sheets("Sheet1").Select
144
+
145
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=2, Criteria1:="M"
146
+
147
+ Sheets("Data").Select
148
+
149
+ Range("C3").Select
150
+
151
+ ActiveCell.FormulaR1C1 = "B"
152
+
153
+ Sheets("Sheet1").Select
154
+
155
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=3, Criteria1:="B"
156
+
157
+ Sheets("Data").Select
158
+
159
+ Range("D3").Select
160
+
161
+ ActiveCell.FormulaR1C1 = "130"
162
+
163
+ Sheets("Sheet1").Select
164
+
165
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=4, Criteria1:="130"
166
+
167
+ Sheets("Data").Select
168
+
169
+ Range("E3").Select
170
+
171
+ ActiveCell.FormulaR1C1 = "20"
172
+
173
+ Sheets("Sheet1").Select
174
+
175
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=5, Criteria1:="20"
176
+
177
+ Sheets("Data").Select
178
+
179
+ Range("F3").Select
180
+
181
+ ActiveCell.FormulaR1C1 = "2600"
182
+
183
+ Sheets("Sheet1").Select
184
+
185
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=6
186
+
187
+ Range("A3:AB6").Select
92
188
 
93
189
  Selection.Copy
94
190
 
95
191
  Sheets("Sheet2").Select
96
192
 
97
- Range("A2").Select
193
+ Range("A15").Select
98
194
 
99
195
  ActiveSheet.Paste
100
196
 
101
197
  Sheets("Data").Select
102
198
 
103
- Range("A3").Select
199
+ Range("A4").Select
104
200
 
105
201
  Application.CutCopyMode = False
106
202
 
107
- ActiveCell.FormulaR1C1 = "りんご"
203
+ ActiveCell.FormulaR1C1 = "バナナ"
108
-
109
- Range("A14:B23").Select
110
-
111
- Range("B14").Activate
112
204
 
113
205
  Sheets("Sheet1").Select
114
206
 
@@ -120,148 +212,70 @@
120
212
 
121
213
  Selection.AutoFilter
122
214
 
123
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=1, Criteria1:="りんご"
124
-
125
- Sheets("Data").Select
126
-
127
- Range("B3").Select
128
-
129
- ActiveCell.FormulaR1C1 = "M"
130
-
131
- Sheets("Sheet1").Select
132
-
133
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=2, Criteria1:="M"
134
-
135
- Sheets("Data").Select
136
-
137
- Range("C3").Select
138
-
139
- ActiveCell.FormulaR1C1 = "B"
140
-
141
- Sheets("Sheet1").Select
142
-
143
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=3, Criteria1:="B"
144
-
145
- Sheets("Data").Select
146
-
147
- Range("D3").Select
148
-
149
- ActiveCell.FormulaR1C1 = "130"
150
-
151
- Sheets("Sheet1").Select
152
-
153
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=4, Criteria1:="130"
154
-
155
- Sheets("Data").Select
156
-
157
- Range("E3").Select
158
-
159
- ActiveCell.FormulaR1C1 = "20"
160
-
161
- Sheets("Sheet1").Select
162
-
163
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=5, Criteria1:="20"
164
-
165
- Sheets("Data").Select
166
-
167
- Range("F3").Select
168
-
169
- ActiveCell.FormulaR1C1 = "2600"
170
-
171
- Sheets("Sheet1").Select
172
-
173
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=6
174
-
175
- Range("A3:AB6").Select
215
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=1, Criteria1:="バナナ"
216
+
217
+ Sheets("Data").Select
218
+
219
+ Range("B4").Select
220
+
221
+ Selection.Copy
222
+
223
+ Sheets("Sheet1").Select
224
+
225
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=2, Criteria1:="L"
226
+
227
+ Sheets("Data").Select
228
+
229
+ Range("C4").Select
230
+
231
+ Application.CutCopyMode = False
232
+
233
+ ActiveCell.FormulaR1C1 = "C"
234
+
235
+ Sheets("Sheet1").Select
236
+
237
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=3, Criteria1:="C"
238
+
239
+ Sheets("Data").Select
240
+
241
+ Range("D4").Select
242
+
243
+ ActiveCell.FormulaR1C1 = "140"
244
+
245
+ Sheets("Sheet1").Select
246
+
247
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=4
248
+
249
+ Sheets("Data").Select
250
+
251
+ Range("E4").Select
252
+
253
+ ActiveCell.FormulaR1C1 = "30"
254
+
255
+ Sheets("Sheet1").Select
256
+
257
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=5
258
+
259
+ Sheets("Data").Select
260
+
261
+ Range("F4").Select
262
+
263
+ ActiveCell.FormulaR1C1 = "4200"
264
+
265
+ Sheets("Sheet1").Select
266
+
267
+ ActiveSheet.Range("$A$1:$AB$195").AutoFilter Field:=6
268
+
269
+ Range("A4:AB195").Select
176
270
 
177
271
  Selection.Copy
178
272
 
179
273
  Sheets("Sheet2").Select
180
274
 
181
- Range("A5").Select
275
+ Range("A17").Select
182
276
 
183
277
  ActiveSheet.Paste
184
278
 
185
- Sheets("Data").Select
186
-
187
- Range("A4").Select
188
-
189
- Application.CutCopyMode = False
190
-
191
- ActiveCell.FormulaR1C1 = "バナナ"
192
-
193
- Sheets("Sheet1").Select
194
-
195
- Rows("1:1").Select
196
-
197
- Range("L1").Activate
198
-
199
- Selection.AutoFilter
200
-
201
- Selection.AutoFilter
202
-
203
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=1, Criteria1:="バナナ"
204
-
205
- Sheets("Data").Select
206
-
207
- Range("B4").Select
208
-
209
- ActiveCell.FormulaR1C1 = "L"
210
-
211
- Sheets("Sheet1").Select
212
-
213
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=2, Criteria1:="L"
214
-
215
- Sheets("Data").Select
216
-
217
- Range("C4").Select
218
-
219
- ActiveCell.FormulaR1C1 = "C"
220
-
221
- Sheets("Sheet1").Select
222
-
223
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=3, Criteria1:="C"
224
-
225
- Sheets("Data").Select
226
-
227
- Range("D4").Select
228
-
229
- ActiveCell.FormulaR1C1 = "140"
230
-
231
- Sheets("Sheet1").Select
232
-
233
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=4
234
-
235
- Sheets("Data").Select
236
-
237
- Range("E4").Select
238
-
239
- ActiveCell.FormulaR1C1 = "30"
240
-
241
- Sheets("Sheet1").Select
242
-
243
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=5
244
-
245
- Sheets("Data").Select
246
-
247
- Range("F4").Select
248
-
249
- ActiveCell.FormulaR1C1 = "4200"
250
-
251
- Sheets("Sheet1").Select
252
-
253
- ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=6
254
-
255
- Range("A4:AB7").Select
256
-
257
- Selection.Copy
258
-
259
- Sheets("Sheet2").Select
260
-
261
- Range("A7").Select
262
-
263
- ActiveSheet.Paste
264
-
265
279
  End Sub
266
280
 
267
281
  ```

10

修正

2020/05/28 10:01

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -36,7 +36,7 @@
36
36
 
37
37
  Sheets("Sheet1").Select
38
38
 
39
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=1, Criteria1:="みかん"
39
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=1, Criteria1:="みかん"
40
40
 
41
41
  Sheets("Data").Select
42
42
 
@@ -46,7 +46,7 @@
46
46
 
47
47
  Sheets("Sheet1").Select
48
48
 
49
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=2, Criteria1:="S"
49
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=2, Criteria1:="S"
50
50
 
51
51
  Sheets("Data").Select
52
52
 
@@ -56,7 +56,7 @@
56
56
 
57
57
  Sheets("Sheet1").Select
58
58
 
59
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=3, Criteria1:="A"
59
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=3, Criteria1:="A"
60
60
 
61
61
  Sheets("Data").Select
62
62
 
@@ -66,7 +66,7 @@
66
66
 
67
67
  Sheets("Sheet1").Select
68
68
 
69
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=4
69
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=4
70
70
 
71
71
  Sheets("Data").Select
72
72
 
@@ -76,7 +76,7 @@
76
76
 
77
77
  Sheets("Sheet1").Select
78
78
 
79
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=5, Criteria1:="10"
79
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=5, Criteria1:="10"
80
80
 
81
81
  Sheets("Data").Select
82
82
 
@@ -86,7 +86,7 @@
86
86
 
87
87
  Sheets("Sheet1").Select
88
88
 
89
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=6
89
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=6
90
90
 
91
91
  Range("A2:AB14").Select
92
92
 
@@ -120,7 +120,7 @@
120
120
 
121
121
  Selection.AutoFilter
122
122
 
123
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=1, Criteria1:="りんご"
123
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=1, Criteria1:="りんご"
124
124
 
125
125
  Sheets("Data").Select
126
126
 
@@ -130,7 +130,7 @@
130
130
 
131
131
  Sheets("Sheet1").Select
132
132
 
133
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=2, Criteria1:="M"
133
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=2, Criteria1:="M"
134
134
 
135
135
  Sheets("Data").Select
136
136
 
@@ -140,7 +140,7 @@
140
140
 
141
141
  Sheets("Sheet1").Select
142
142
 
143
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=3, Criteria1:="B"
143
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=3, Criteria1:="B"
144
144
 
145
145
  Sheets("Data").Select
146
146
 
@@ -150,7 +150,7 @@
150
150
 
151
151
  Sheets("Sheet1").Select
152
152
 
153
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=4, Criteria1:="130"
153
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=4, Criteria1:="130"
154
154
 
155
155
  Sheets("Data").Select
156
156
 
@@ -160,7 +160,7 @@
160
160
 
161
161
  Sheets("Sheet1").Select
162
162
 
163
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=5, Criteria1:="20"
163
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=5, Criteria1:="20"
164
164
 
165
165
  Sheets("Data").Select
166
166
 
@@ -170,7 +170,7 @@
170
170
 
171
171
  Sheets("Sheet1").Select
172
172
 
173
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=6
173
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=6
174
174
 
175
175
  Range("A3:AB6").Select
176
176
 
@@ -200,7 +200,7 @@
200
200
 
201
201
  Selection.AutoFilter
202
202
 
203
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=1, Criteria1:="バナナ"
203
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=1, Criteria1:="バナナ"
204
204
 
205
205
  Sheets("Data").Select
206
206
 
@@ -210,7 +210,7 @@
210
210
 
211
211
  Sheets("Sheet1").Select
212
212
 
213
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=2, Criteria1:="L"
213
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=2, Criteria1:="L"
214
214
 
215
215
  Sheets("Data").Select
216
216
 
@@ -220,7 +220,7 @@
220
220
 
221
221
  Sheets("Sheet1").Select
222
222
 
223
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=3, Criteria1:="C"
223
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=3, Criteria1:="C"
224
224
 
225
225
  Sheets("Data").Select
226
226
 
@@ -230,7 +230,7 @@
230
230
 
231
231
  Sheets("Sheet1").Select
232
232
 
233
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=4
233
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=4
234
234
 
235
235
  Sheets("Data").Select
236
236
 
@@ -240,7 +240,7 @@
240
240
 
241
241
  Sheets("Sheet1").Select
242
242
 
243
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=5
243
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=5
244
244
 
245
245
  Sheets("Data").Select
246
246
 
@@ -250,7 +250,7 @@
250
250
 
251
251
  Sheets("Sheet1").Select
252
252
 
253
- ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=6
253
+ ActiveSheet.Range("$A$1:$AB$169").AutoFilter Field:=6
254
254
 
255
255
  Range("A4:AB7").Select
256
256
 

9

誤記

2020/05/28 09:46

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -24,7 +24,7 @@
24
24
 
25
25
 
26
26
 
27
- ```ここに言語を入力
27
+ ```
28
28
 
29
29
  Private Sub CommandButton3_Click()
30
30
 

8

修正

2020/05/28 09:38

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -28,92 +28,240 @@
28
28
 
29
29
  Private Sub CommandButton3_Click()
30
30
 
31
- Dim kk As Integer
32
-
33
- Dim dd As Variant
34
-
35
- Dim Kw(7) As Variant
36
-
37
-
38
-
39
- With Worksheets("Data")
40
-
41
- maxRow = Range("A" & Rows.Count).End(xlUp).Row
42
-
43
- Debug.Print (maxRow)
44
-
45
-
46
-
47
- '検索
48
-
49
- For kk = 1 To 6
50
-
51
- For dd = 2 To maxRow
52
-
53
-
54
-
55
- Kw(kk) = .Cells(dd, kk).Value
56
-
57
- Debug.Print "Kw(" & kk & "):" & Kw(kk)
58
-
59
- Next
60
-
61
- Next
62
-
63
- End With
64
-
65
-
66
-
67
-
68
-
69
- With Worksheets("Sheet1").Range("A1")
70
-
71
- .AutoFilter 'AutoFilter解除
72
-
73
- .AutoFilter 1, Kw(1)
74
-
75
- .AutoFilter 2, Kw(2)
76
-
77
- .AutoFilter 3, Kw(3)
78
-
79
- .AutoFilter 4, Kw(4)
80
-
81
- .AutoFilter 5, Kw(5)
82
-
83
- .AutoFilter 6, Kw(6)
84
-
85
-
86
-
87
-
88
-
89
- End With
90
-
91
-
92
-
93
- '必要情報のみ"Sheet2"にコピー
94
-
95
- '抽出データの最終行を求める
96
-
97
- myRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
98
-
99
- '抽出データをコピーして貼り付け
100
-
101
- With Sheets("Sheet1").Range("A1")
102
-
103
- .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
104
-
105
- End With
106
-
107
-
108
-
109
- Worksheets("Sheet1").Range("A1").AutoFilter
110
-
111
- Worksheets("Sheet2").Activate
112
-
113
- Range("A1").Select
114
-
115
- End Sub
116
-
117
-
118
-
119
- ```
31
+ Sheets("Data").Select
32
+
33
+ Range("A2").Select
34
+
35
+ ActiveCell.FormulaR1C1 = "みかん"
36
+
37
+ Sheets("Sheet1").Select
38
+
39
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=1, Criteria1:="みかん"
40
+
41
+ Sheets("Data").Select
42
+
43
+ Range("B2").Select
44
+
45
+ ActiveCell.FormulaR1C1 = "S"
46
+
47
+ Sheets("Sheet1").Select
48
+
49
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=2, Criteria1:="S"
50
+
51
+ Sheets("Data").Select
52
+
53
+ Range("C2").Select
54
+
55
+ ActiveCell.FormulaR1C1 = "A"
56
+
57
+ Sheets("Sheet1").Select
58
+
59
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=3, Criteria1:="A"
60
+
61
+ Sheets("Data").Select
62
+
63
+ Range("D2").Select
64
+
65
+ ActiveCell.FormulaR1C1 = "120"
66
+
67
+ Sheets("Sheet1").Select
68
+
69
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=4
70
+
71
+ Sheets("Data").Select
72
+
73
+ Range("E2").Select
74
+
75
+ ActiveCell.FormulaR1C1 = "10"
76
+
77
+ Sheets("Sheet1").Select
78
+
79
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=5, Criteria1:="10"
80
+
81
+ Sheets("Data").Select
82
+
83
+ Range("F2").Select
84
+
85
+ ActiveCell.FormulaR1C1 = "1200"
86
+
87
+ Sheets("Sheet1").Select
88
+
89
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=6
90
+
91
+ Range("A2:AB14").Select
92
+
93
+ Selection.Copy
94
+
95
+ Sheets("Sheet2").Select
96
+
97
+ Range("A2").Select
98
+
99
+ ActiveSheet.Paste
100
+
101
+ Sheets("Data").Select
102
+
103
+ Range("A3").Select
104
+
105
+ Application.CutCopyMode = False
106
+
107
+ ActiveCell.FormulaR1C1 = "りんご"
108
+
109
+ Range("A14:B23").Select
110
+
111
+ Range("B14").Activate
112
+
113
+ Sheets("Sheet1").Select
114
+
115
+ Rows("1:1").Select
116
+
117
+ Range("L1").Activate
118
+
119
+ Selection.AutoFilter
120
+
121
+ Selection.AutoFilter
122
+
123
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=1, Criteria1:="りんご"
124
+
125
+ Sheets("Data").Select
126
+
127
+ Range("B3").Select
128
+
129
+ ActiveCell.FormulaR1C1 = "M"
130
+
131
+ Sheets("Sheet1").Select
132
+
133
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=2, Criteria1:="M"
134
+
135
+ Sheets("Data").Select
136
+
137
+ Range("C3").Select
138
+
139
+ ActiveCell.FormulaR1C1 = "B"
140
+
141
+ Sheets("Sheet1").Select
142
+
143
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=3, Criteria1:="B"
144
+
145
+ Sheets("Data").Select
146
+
147
+ Range("D3").Select
148
+
149
+ ActiveCell.FormulaR1C1 = "130"
150
+
151
+ Sheets("Sheet1").Select
152
+
153
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=4, Criteria1:="130"
154
+
155
+ Sheets("Data").Select
156
+
157
+ Range("E3").Select
158
+
159
+ ActiveCell.FormulaR1C1 = "20"
160
+
161
+ Sheets("Sheet1").Select
162
+
163
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=5, Criteria1:="20"
164
+
165
+ Sheets("Data").Select
166
+
167
+ Range("F3").Select
168
+
169
+ ActiveCell.FormulaR1C1 = "2600"
170
+
171
+ Sheets("Sheet1").Select
172
+
173
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=6
174
+
175
+ Range("A3:AB6").Select
176
+
177
+ Selection.Copy
178
+
179
+ Sheets("Sheet2").Select
180
+
181
+ Range("A5").Select
182
+
183
+ ActiveSheet.Paste
184
+
185
+ Sheets("Data").Select
186
+
187
+ Range("A4").Select
188
+
189
+ Application.CutCopyMode = False
190
+
191
+ ActiveCell.FormulaR1C1 = "バナナ"
192
+
193
+ Sheets("Sheet1").Select
194
+
195
+ Rows("1:1").Select
196
+
197
+ Range("L1").Activate
198
+
199
+ Selection.AutoFilter
200
+
201
+ Selection.AutoFilter
202
+
203
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=1, Criteria1:="バナナ"
204
+
205
+ Sheets("Data").Select
206
+
207
+ Range("B4").Select
208
+
209
+ ActiveCell.FormulaR1C1 = "L"
210
+
211
+ Sheets("Sheet1").Select
212
+
213
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=2, Criteria1:="L"
214
+
215
+ Sheets("Data").Select
216
+
217
+ Range("C4").Select
218
+
219
+ ActiveCell.FormulaR1C1 = "C"
220
+
221
+ Sheets("Sheet1").Select
222
+
223
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=3, Criteria1:="C"
224
+
225
+ Sheets("Data").Select
226
+
227
+ Range("D4").Select
228
+
229
+ ActiveCell.FormulaR1C1 = "140"
230
+
231
+ Sheets("Sheet1").Select
232
+
233
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=4
234
+
235
+ Sheets("Data").Select
236
+
237
+ Range("E4").Select
238
+
239
+ ActiveCell.FormulaR1C1 = "30"
240
+
241
+ Sheets("Sheet1").Select
242
+
243
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=5
244
+
245
+ Sheets("Data").Select
246
+
247
+ Range("F4").Select
248
+
249
+ ActiveCell.FormulaR1C1 = "4200"
250
+
251
+ Sheets("Sheet1").Select
252
+
253
+ ActiveSheet.Range("$A$1:$AB$500").AutoFilter Field:=6
254
+
255
+ Range("A4:AB7").Select
256
+
257
+ Selection.Copy
258
+
259
+ Sheets("Sheet2").Select
260
+
261
+ Range("A7").Select
262
+
263
+ ActiveSheet.Paste
264
+
265
+ End Sub
266
+
267
+ ```

7

修正

2020/05/28 09:38

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -76,6 +76,14 @@
76
76
 
77
77
  .AutoFilter 3, Kw(3)
78
78
 
79
+ .AutoFilter 4, Kw(4)
80
+
81
+ .AutoFilter 5, Kw(5)
82
+
83
+ .AutoFilter 6, Kw(6)
84
+
85
+
86
+
79
87
 
80
88
 
81
89
  End With

6

修正

2020/05/27 07:52

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -25,6 +25,8 @@
25
25
 
26
26
 
27
27
  ```ここに言語を入力
28
+
29
+ Private Sub CommandButton3_Click()
28
30
 
29
31
  Dim kk As Integer
30
32
 

5

修正

2020/05/27 07:32

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -9,18 +9,6 @@
9
9
  ・抽出結果は１行ごとにひとかたまりで、Sheet1でフィルターをかけます。手動でやりますとおよそ10行くらい抽出されます。ですので、Sheet2は30行くらいになります。
10
10
 
11
11
 
12
-
13
- <エラー箇所＞
14
-
15
- Kw(k_no) = .Cells(d_no, k_no).Value
16
-
17
- ここでデバックとなり、色々と試してみましたがうまくいきません。
18
-
19
- すみませんが、ご教示のほどよろしくお願いいたします。
20
-
21
-
22
-
23
- 追記
24
12
 
25
13
  <エラー内容＞
26
14
 
@@ -38,39 +26,33 @@
38
26
 
39
27
  ```ここに言語を入力
40
28
 
41
- Private Sub CommandButton1_Click()
29
+ Dim kk As Integer
42
30
 
43
- Dim Kw As
31
+ Dim dd As Variant
44
32
 
45
- Dim k_no As Long 'A列からF列
46
-
47
- Dim d_no As Long '抽出した行数
48
-
49
- Dim maxRow As Long
33
+ Dim Kw(7) As Variant
50
-
51
-
52
34
 
53
35
 
54
36
 
55
- With Worksheets("Data")
37
+ With Worksheets("Data")
56
38
 
57
39
  maxRow = Range("A" & Rows.Count).End(xlUp).Row
58
40
 
59
41
  Debug.Print (maxRow)
60
42
 
43
+
44
+
45
+ '検索
46
+
47
+ For kk = 1 To 6
48
+
49
+ For dd = 2 To maxRow
50
+
61
51
 
62
52
 
63
- For k_no = 1 To 6
53
+ Kw(kk) = .Cells(dd, kk).Value
64
54
 
65
- For d_no = 2 To maxRow
66
-
67
-
68
-
69
- Kw(k_no) = .Cells(d_no, k_no).Value
70
-
71
- Debug.Print "Kw(" & k_no & "):" & Kw(k_no)
55
+ Debug.Print "Kw(" & kk & "):" & Kw(kk)
72
-
73
-
74
56
 
75
57
  Next
76
58
 
@@ -78,34 +60,50 @@
78
60
 
79
61
  End With
80
62
 
81
-
63
+
82
64
 
83
- '検索keywordでFiltering
65
+
84
66
 
85
67
  With Worksheets("Sheet1").Range("A1")
86
68
 
87
- .AutoFilter 1, Kw(1)
69
+ .AutoFilter 'AutoFilter解除
88
70
 
89
- .AutoFilter 2, Kw(2)
71
+ .AutoFilter 1, Kw(1)
90
72
 
91
- .AutoFilter 3, Kw(3)
73
+ .AutoFilter 2, Kw(2)
92
74
 
93
- .AutoFilter 4, Kw(4)
75
+ .AutoFilter 3, Kw(3)
94
76
 
95
- .AutoFilter 5, Kw(5)
77
+
96
78
 
97
- .AutoFilter 6, Kw(6)
79
+ End With
98
80
 
99
-
81
+
100
82
 
83
+ '必要情報のみ"Sheet2"にコピー
84
+
85
+ '抽出データの最終行を求める
86
+
87
+ myRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
88
+
89
+ '抽出データをコピーして貼り付け
90
+
91
+ With Sheets("Sheet1").Range("A1")
92
+
101
- .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
93
+ .CurrentRegion.Copy Sheets("Sheet2").Range("A1")
94
+
95
+ End With
96
+
97
+
98
+
99
+ Worksheets("Sheet1").Range("A1").AutoFilter
100
+
101
+ Worksheets("Sheet2").Activate
102
+
103
+ Range("A1").Select
104
+
105
+ End Sub
102
106
 
103
107
 
104
108
 
105
-
106
-
107
- End With
108
-
109
- End Sub
110
-
111
109
  ```

4

誤記

2020/05/27 07:32

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -24,7 +24,7 @@
24
24
 
25
25
  <エラー内容＞
26
26
 
27
- ”Data”シートの２行目のデータしか検索されず、他の３行目、４行目は４検索されませんでした。
27
+ ”Data”シートの２行目のデータしか検索されず、他の３行目、４行目は検索されませんでした。
28
28
 
29
29
  For Nextの書き方が違っているのか、それとも他も含めて問題があるのかが不明です。よろしくお願いいたします。
30
30
 

3

追記しました。

2020/05/26 14:18

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -30,7 +30,7 @@
30
30
 
31
31
 
32
32
 
33
-
33
+ ”Data"シート
34
34
 
35
35
  ![イメージ説明](bc7619b1827e5b4a2fe35f7a211fb1ec.png)
36
36
 

2

誤記

2020/05/26 14:16

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -24,7 +24,7 @@
24
24
 
25
25
  <エラー内容＞
26
26
 
27
- ２行目のデータしか検索されず、他の３行目、４行目は４検索されませんでした。
27
+ ”Data”シートの２行目のデータしか検索されず、他の３行目、４行目は４検索されませんでした。
28
28
 
29
29
  For Nextの書き方が違っているのか、それとも他も含めて問題があるのかが不明です。よろしくお願いいたします。
30
30
 

1

詳細追記しました。

2020/05/26 14:15

投稿

koko2
koko2

スコア21

test CHANGED
File without changes
test CHANGED
@@ -17,6 +17,18 @@
17
17
  ここでデバックとなり、色々と試してみましたがうまくいきません。
18
18
 
19
19
  すみませんが、ご教示のほどよろしくお願いいたします。
20
+
21
+
22
+
23
+ 追記
24
+
25
+ <エラー内容＞
26
+
27
+ ２行目のデータしか検索されず、他の３行目、４行目は４検索されませんでした。
28
+
29
+ For Nextの書き方が違っているのか、それとも他も含めて問題があるのかが不明です。よろしくお願いいたします。
30
+
31
+
20
32
 
21
33
 
22
34