質問編集履歴

2

テンプレートを修正しました。

2021/05/13 02:48

投稿

shogakusha
shogakusha

スコア12

test CHANGED
File without changes
test CHANGED
@@ -10,266 +10,260 @@
10
10
 
11
11
  変数LastRowの値が、Do~Loop内で更新されず、上書きされてしまう。
12
12
 
13
+
14
+
15
+ ```VBA
16
+
17
+ Option Explicit
18
+
19
+
20
+
21
+ Dim ID As Integer
22
+
23
+ Dim Name_F As String
24
+
25
+ Dim Name_L As String
26
+
27
+ Dim Role As String
28
+
29
+ Dim Year_E As Integer
30
+
31
+ Dim Month_E As Integer
32
+
33
+ Dim Day_E As Integer
34
+
35
+ Dim Year_L As Integer
36
+
37
+ Dim Month_L As Integer
38
+
39
+ Dim Day_L As Integer
40
+
41
+ Dim Experience As String
42
+
43
+ Dim Language_1 As String
44
+
45
+ Dim Language_2 As String
46
+
47
+ Dim Language_3 As String
48
+
49
+ Dim Language_4 As String
50
+
51
+
52
+
53
+
54
+
55
+ Const Col_ID = "A"
56
+
57
+ Const Col_Name_F = "B"
58
+
59
+ Const Col_Name_L = "C"
60
+
61
+ Const Col_Role = "D"
62
+
63
+ Const Col_Year_E = "E"
64
+
65
+ Const Col_Month_E = "F"
66
+
67
+ Const Col_Day_E = "G"
68
+
69
+ Const Col_Year_L = "H"
70
+
71
+ Const Col_Month_L = "I"
72
+
73
+ Const Col_Day_L = "J"
74
+
75
+ Const Col_Experience = "K"
76
+
77
+ Const Col_Language_1 = "L"
78
+
79
+ Const Col_Language_2 = "M"
80
+
81
+ Const Col_Language_3 = "N"
82
+
83
+ Const Col_Language_4 = "O"
84
+
85
+
86
+
87
+ Sub Kensaku_Click()
88
+
89
+
90
+
91
+ '変数宣言
92
+
93
+ Dim sht_toroku As Worksheet
94
+
95
+ Dim sht_shain As Worksheet
96
+
97
+ Dim sht_itiran As Worksheet
98
+
99
+ Dim FoundCell As Range
100
+
101
+ Dim LastRow As Integer
102
+
103
+ Dim FirstCell As Range
104
+
105
+
106
+
107
+ Set sht_toroku = Worksheets("社員情報管理_登録")
108
+
109
+ Set sht_shain = Worksheets("社員マスタ")
110
+
111
+ Set sht_itiran = Worksheets("社員情報一覧")
112
+
113
+ LastRow = sht_itiran.Cells(Rows.Count, 2).End(xlUp).Row + 1
114
+
115
+
116
+
117
+ '1件目の社員マスタ内のデータを検索
118
+
119
+ Set FoundCell = sht_shain.Range("A2:O1000").Find(sht_toroku.Range("E3").Value)
120
+
121
+
122
+
123
+ If FoundCell Is Nothing Then
124
+
125
+ MsgBox "検索対象が見つかりませんでした。"
126
+
127
+ Exit Sub
128
+
129
+ Else
130
+
131
+ Set FirstCell = FoundCell
132
+
133
+
134
+
135
+ '社員マスタシートの情報を取得_1件目
136
+
137
+ Call getShainData(sht_shain, FoundCell)
138
+
139
+
140
+
141
+ '取得したデータを社員一覧シートに転記_1件目
142
+
143
+ Call outShainData(sht_itiran, LastRow)
144
+
145
+ End If
146
+
147
+
148
+
149
+ 'ループ Start
150
+
151
+ Do
152
+
153
+ '次の条件に合致するデータの検索
154
+
155
+ Set FoundCell = sht_shain.Cells.FindNext(FoundCell)
156
+
157
+
158
+
159
+ '検索条件に合致するデータを検索し終わった場合、終了
160
+
161
+ If FoundCell.Address = FirstCell.Address Then
162
+
163
+ Exit Do
164
+
165
+ Else
166
+
167
+ '社員マスタシートの情報を取得_2件目以降
168
+
169
+ Call getShainData(sht_shain, FoundCell)
170
+
171
+
172
+
173
+ '取得したデータを社員一覧シートに転記_2件目以降
174
+
175
+ Call outShainData(sht_itiran, LastRow)
176
+
177
+ End If
178
+
179
+ Loop
180
+
181
+ End Sub
182
+
183
+
184
+
185
+ Sub getShainData(sht_shain As Worksheet, FoundCell As Range)
186
+
187
+
188
+
189
+ ID = sht_shain.Cells(FoundCell.Row, Col_ID).Value
190
+
191
+ Name_F = sht_shain.Cells(FoundCell.Row, Col_Name_F).Value
192
+
193
+ Name_L = sht_shain.Cells(FoundCell.Row, Col_Name_L).Value
194
+
195
+ Role = sht_shain.Cells(FoundCell.Row, Col_Role).Value
196
+
197
+ Year_E = sht_shain.Cells(FoundCell.Row, Col_Year_E).Value
198
+
199
+ Month_E = sht_shain.Cells(FoundCell.Row, Col_Month_E).Value
200
+
201
+ Day_E = sht_shain.Cells(FoundCell.Row, Col_Day_E).Value
202
+
203
+ Year_L = sht_shain.Cells(FoundCell.Row, Col_Year_L).Value
204
+
205
+ Month_L = sht_shain.Cells(FoundCell.Row, Col_Month_L).Value
206
+
207
+ Day_L = sht_shain.Cells(FoundCell.Row, Col_Day_L).Value
208
+
209
+ Experience = sht_shain.Cells(FoundCell.Row, Col_Experience).Value
210
+
211
+ Language_1 = sht_shain.Cells(FoundCell.Row, Col_Language_1).Value
212
+
213
+ Language_2 = sht_shain.Cells(FoundCell.Row, Col_Language_2).Value
214
+
215
+ Language_3 = sht_shain.Cells(FoundCell.Row, Col_Language_3).Value
216
+
217
+ Language_4 = sht_shain.Cells(FoundCell.Row, Col_Language_4).Value
218
+
219
+
220
+
221
+ End Sub
222
+
223
+
224
+
225
+ Sub outShainData(sht_itiran As Worksheet, LastRow As Integer)
226
+
227
+
228
+
229
+ sht_itiran.Range("A" & LastRow).Value = ID
230
+
231
+ sht_itiran.Range("B" & LastRow).Value = Name_F
232
+
233
+ sht_itiran.Range("C" & LastRow).Value = Name_L
234
+
235
+ sht_itiran.Range("D" & LastRow).Value = Role
236
+
237
+ sht_itiran.Range("E" & LastRow).Value = Year_E
238
+
239
+ sht_itiran.Range("F" & LastRow).Value = Month_E
240
+
241
+ sht_itiran.Range("G" & LastRow).Value = Day_E
242
+
243
+ sht_itiran.Range("H" & LastRow).Value = Year_L
244
+
245
+ sht_itiran.Range("I" & LastRow).Value = Month_L
246
+
247
+ sht_itiran.Range("J" & LastRow).Value = Day_L
248
+
249
+ sht_itiran.Range("K" & LastRow).Value = Experience
250
+
251
+ sht_itiran.Range("L" & LastRow).Value = Language_1
252
+
253
+ sht_itiran.Range("M" & LastRow).Value = Language_2
254
+
255
+ sht_itiran.Range("N" & LastRow).Value = Language_3
256
+
257
+ sht_itiran.Range("O" & LastRow).Value = Language_4
258
+
259
+
260
+
261
+ End Sub
262
+
13
263
  ```
14
264
 
15
265
 
16
266
 
17
- ### 該当のソースコード
18
-
19
-
20
-
21
- ```VBA
22
-
23
- Option Explicit
24
-
25
-
26
-
27
- Dim ID As Integer
28
-
29
- Dim Name_F As String
30
-
31
- Dim Name_L As String
32
-
33
- Dim Role As String
34
-
35
- Dim Year_E As Integer
36
-
37
- Dim Month_E As Integer
38
-
39
- Dim Day_E As Integer
40
-
41
- Dim Year_L As Integer
42
-
43
- Dim Month_L As Integer
44
-
45
- Dim Day_L As Integer
46
-
47
- Dim Experience As String
48
-
49
- Dim Language_1 As String
50
-
51
- Dim Language_2 As String
52
-
53
- Dim Language_3 As String
54
-
55
- Dim Language_4 As String
56
-
57
-
58
-
59
-
60
-
61
- Const Col_ID = "A"
62
-
63
- Const Col_Name_F = "B"
64
-
65
- Const Col_Name_L = "C"
66
-
67
- Const Col_Role = "D"
68
-
69
- Const Col_Year_E = "E"
70
-
71
- Const Col_Month_E = "F"
72
-
73
- Const Col_Day_E = "G"
74
-
75
- Const Col_Year_L = "H"
76
-
77
- Const Col_Month_L = "I"
78
-
79
- Const Col_Day_L = "J"
80
-
81
- Const Col_Experience = "K"
82
-
83
- Const Col_Language_1 = "L"
84
-
85
- Const Col_Language_2 = "M"
86
-
87
- Const Col_Language_3 = "N"
88
-
89
- Const Col_Language_4 = "O"
90
-
91
-
92
-
93
- Sub Kensaku_Click()
94
-
95
-
96
-
97
- '変数宣言
98
-
99
- Dim sht_toroku As Worksheet
100
-
101
- Dim sht_shain As Worksheet
102
-
103
- Dim sht_itiran As Worksheet
104
-
105
- Dim FoundCell As Range
106
-
107
- Dim LastRow As Integer
108
-
109
- Dim FirstCell As Range
110
-
111
-
112
-
113
- Set sht_toroku = Worksheets("社員情報管理_登録")
114
-
115
- Set sht_shain = Worksheets("社員マスタ")
116
-
117
- Set sht_itiran = Worksheets("社員情報一覧")
118
-
119
- LastRow = sht_itiran.Cells(Rows.Count, 2).End(xlUp).Row + 1
120
-
121
-
122
-
123
- '1件目の社員マスタ内のデータを検索
124
-
125
- Set FoundCell = sht_shain.Range("A2:O1000").Find(sht_toroku.Range("E3").Value)
126
-
127
-
128
-
129
- If FoundCell Is Nothing Then
130
-
131
- MsgBox "検索対象が見つかりませんでした。"
132
-
133
- Exit Sub
134
-
135
- Else
136
-
137
- Set FirstCell = FoundCell
138
-
139
-
140
-
141
- '社員マスタシートの情報を取得_1件目
142
-
143
- Call getShainData(sht_shain, FoundCell)
144
-
145
-
146
-
147
- '取得したデータを社員一覧シートに転記_1件目
148
-
149
- Call outShainData(sht_itiran, LastRow)
150
-
151
- End If
152
-
153
-
154
-
155
- 'ループ Start
156
-
157
- Do
158
-
159
- '次の条件に合致するデータの検索
160
-
161
- Set FoundCell = sht_shain.Cells.FindNext(FoundCell)
162
-
163
-
164
-
165
- '検索条件に合致するデータを検索し終わった場合、終了
166
-
167
- If FoundCell.Address = FirstCell.Address Then
168
-
169
- Exit Do
170
-
171
- Else
172
-
173
- '社員マスタシートの情報を取得_2件目以降
174
-
175
- Call getShainData(sht_shain, FoundCell)
176
-
177
-
178
-
179
- '取得したデータを社員一覧シートに転記_2件目以降
180
-
181
- Call outShainData(sht_itiran, LastRow)
182
-
183
- End If
184
-
185
- Loop
186
-
187
- End Sub
188
-
189
-
190
-
191
- Sub getShainData(sht_shain As Worksheet, FoundCell As Range)
192
-
193
-
194
-
195
- ID = sht_shain.Cells(FoundCell.Row, Col_ID).Value
196
-
197
- Name_F = sht_shain.Cells(FoundCell.Row, Col_Name_F).Value
198
-
199
- Name_L = sht_shain.Cells(FoundCell.Row, Col_Name_L).Value
200
-
201
- Role = sht_shain.Cells(FoundCell.Row, Col_Role).Value
202
-
203
- Year_E = sht_shain.Cells(FoundCell.Row, Col_Year_E).Value
204
-
205
- Month_E = sht_shain.Cells(FoundCell.Row, Col_Month_E).Value
206
-
207
- Day_E = sht_shain.Cells(FoundCell.Row, Col_Day_E).Value
208
-
209
- Year_L = sht_shain.Cells(FoundCell.Row, Col_Year_L).Value
210
-
211
- Month_L = sht_shain.Cells(FoundCell.Row, Col_Month_L).Value
212
-
213
- Day_L = sht_shain.Cells(FoundCell.Row, Col_Day_L).Value
214
-
215
- Experience = sht_shain.Cells(FoundCell.Row, Col_Experience).Value
216
-
217
- Language_1 = sht_shain.Cells(FoundCell.Row, Col_Language_1).Value
218
-
219
- Language_2 = sht_shain.Cells(FoundCell.Row, Col_Language_2).Value
220
-
221
- Language_3 = sht_shain.Cells(FoundCell.Row, Col_Language_3).Value
222
-
223
- Language_4 = sht_shain.Cells(FoundCell.Row, Col_Language_4).Value
224
-
225
-
226
-
227
- End Sub
228
-
229
-
230
-
231
- Sub outShainData(sht_itiran As Worksheet, LastRow As Integer)
232
-
233
-
234
-
235
- sht_itiran.Range("A" & LastRow).Value = ID
236
-
237
- sht_itiran.Range("B" & LastRow).Value = Name_F
238
-
239
- sht_itiran.Range("C" & LastRow).Value = Name_L
240
-
241
- sht_itiran.Range("D" & LastRow).Value = Role
242
-
243
- sht_itiran.Range("E" & LastRow).Value = Year_E
244
-
245
- sht_itiran.Range("F" & LastRow).Value = Month_E
246
-
247
- sht_itiran.Range("G" & LastRow).Value = Day_E
248
-
249
- sht_itiran.Range("H" & LastRow).Value = Year_L
250
-
251
- sht_itiran.Range("I" & LastRow).Value = Month_L
252
-
253
- sht_itiran.Range("J" & LastRow).Value = Day_L
254
-
255
- sht_itiran.Range("K" & LastRow).Value = Experience
256
-
257
- sht_itiran.Range("L" & LastRow).Value = Language_1
258
-
259
- sht_itiran.Range("M" & LastRow).Value = Language_2
260
-
261
- sht_itiran.Range("N" & LastRow).Value = Language_3
262
-
263
- sht_itiran.Range("O" & LastRow).Value = Language_4
264
-
265
-
266
-
267
- End Sub
268
-
269
- ```
270
-
271
-
272
-
273
267
  ### 試したこと
274
268
 
275
269
 

1

テンプレートを修正しました。

2021/05/13 02:48

投稿

shogakusha
shogakusha

スコア12

test CHANGED
File without changes
test CHANGED
@@ -267,3 +267,19 @@
267
267
  End Sub
268
268
 
269
269
  ```
270
+
271
+
272
+
273
+ ### 試したこと
274
+
275
+
276
+
277
+ ここに問題に対して試したことを記載してください。
278
+
279
+
280
+
281
+ ### 補足情報(FW/ツールのバージョンなど)
282
+
283
+
284
+
285
+ ここにより詳細な情報を記載してください。