特定の範囲のデータをリスト化する場合、定番なのは行と列のループをネストさせる方法。
行の変数(r)と列(c)の変数をCellsで利用すればOK。見出し等は行や列の数値を固定にする。
|
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 |
Sub test() Set fw = Sheets("f") Set tw = Sheets("t") tw.Cells.Delete targetRow = 1 For c = 3 To 16 For r = 3 To 258 Step 5 staff = fw.Cells(r, 1).Value '列が固定の場合 ym = fw.Cells(2, c).Value ' 行が固定の場合 price1 = fw.Cells(r, c).Value price2 = fw.Cells(r + 1, c).Value note1 = fw.Cells(r + 2, c).Value note2 = fw.Cells(r + 3, c).Value note3 = fw.Cells(r + 4, c).Value tw.Cells(targetRow, 1).Value = staff tw.Cells(targetRow, 2).Value = ym tw.Cells(targetRow, 3).Value = price1 tw.Cells(targetRow, 4).Value = price2 tw.Cells(targetRow, 5).Value = note1 tw.Cells(targetRow, 6).Value = note2 tw.Cells(targetRow, 7).Value = note3 targetRow = targetRow + 1 Next r Next c 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 |
Sub test2() r_start = 2 c_start = 3 r_count = 4 c_count = 5 l_start = 3 For r = r_start To r_start + r_count - 1 For c = c_start To c_start + c_count - 1 ActiveSheet.Cells(r, c).Value = r & "行" & ", " & c & "列" Next Next For r = r_start To r_start + r_count - 1 For c = c_start To c_start + c_count - 1 ActiveSheet.Cells(l_start, 1).Value = ActiveSheet.Cells(r, c).Value l_start = l_start + 1 Next Next End Sub |