ExcelでC2からデータが縦に5つずつ入力されています。
そのデータをG列(G1から開始)に横にデータをはりつけたいのですがどこがおかしいでしょうか?
添付画像は、実行した結果こうしたいという意味ではりつけてます。
エラーになる個所は
Cells(i, Q + 7) = myList(Z, 1) ' G列からK列にコピーです
全体コード↓↓
Sub 縦リストを横リストに変換()
Dim i As Long
Dim Q As Long
Dim Z As Long
Dim lastRow As Long
Dim myList As Variant
' C列のデータを配列に格納
myList = Range("C2", Range("C" & Rows.Count).End(xlUp))
' 最終行を取得
lastRow = Cells(Rows.Count, 3).End(xlUp).Row
Z = 1
' 列をループ
For i = 1 To lastRow / 5
' 配列を5つ分ループ
For Q = 0 To 4
Cells(i, Q + 7) = myList(Z, 1) ' G列からK列にコピー
Z = Z + 1
Next Q
Next i
End Sub


No.3
- 回答日時:
No1です。
>関数だとファイルがかなり重くなっていました。
もしも、お使いのバージョンがOffice365等であれば、G1セルに
=INDEX(C:C,ROW(OFFSET(A1,,,ROUNDUP((MAX(FILTER(ROW(C:C),C:C<>""))-1)/5,0)))*5-4+COLUMN(A1:E1))&""
の式を入力すれば、下方、右方の必要な範囲に自動でスピルされます。
(こちらなら、1セルだけの関数入力なので重くなることはないと思います)
※ 上記は、「C列に値が入っているセルまで」全て反映しますが、「5つのデータが揃っている行まで」とするなら、ROUNDUP関数をROUNDDOWN関数に変えればそのようになります。
どうしてもVBAで行いたいのなら、以下のようなものでも可能と思います。
Sub Sample()
For r = 2 To Cells(Rows.Count, 3).End(xlUp).Row Step 5
Cells(Int(r / 5) + 1, 7).Resize(, 5).Value = _
Application.Transpose(Cells(r, 3).Resize(5).Value)
Next r
End Sub
※ 上記は数式の例と同様に「C列に値が入っているセルまで」としていますが、「5つのデータが揃っている行まで」としたいのなら、For文の繰り返し制御の最大値を同様に切り捨てることで可能になります。
No.2ベストアンサー
- 回答日時:
個数の項目まで、値がきちんと埋まってないと、
エラーメッセージ「インデックスが有効な範囲にありません)が表示されます。
C列の最後のデータは、個数のある行になっていますか?
添付図の場合、C16(黄色のセル)が空白の場合、エラーになります。


No.1
- 回答日時:
こんにちは
添付図が正しいのなら、元データはC1セルから5行分ずつのはず。
これに対して、
>myList = Range("C2", Range("C" & Rows.Count).End(xlUp))
としているので、例示の図で言えば、C2:C15の範囲の値を取得しています。
出力する際に、1行分ずれてしまうし、最後に値が存在しないのでエラーになる。
・・ということが起きていると思いますが?
とりあえず、C1セルから取得するようにすれば、意図した結果になるでしょう。
別法として、G1セルに
=INDEX(OFFSET($C$1,ROW(A1)*5-5,0,5),COLUMN(A1))&""
の関数式を入れておいて、G:K列の適当な行までフィルコピーしておけば、
VBAを実行しなくても、入力内容が即時に反映されるようになります。
(365環境なら、配列計算式にしておけばフィルコピーも不要になります)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 数字が「0」の列を削除するため、下記のコードを実行しましたが、コンパイルエラーSubまたはFunct 3 2022/12/04 00:00
- Visual Basic(VBA) A2~I4179列にあるリストを支社名ごとにシートに分けたいです。 各シート名はA列にある支社名とし 3 2023/08/29 16:46
- Visual Basic(VBA) VBA listBoxについて 2 2024/03/26 16:14
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) VBA横データを縦にしたいです 2 2023/08/08 19:38
- Excel(エクセル) Excel データの並び替え 1 2024/01/28 18:11
- Excel(エクセル) B列に文字がはいったらA列に数字が入るマクロードを完成させたい 4 2023/04/21 01:58
- Visual Basic(VBA) Sub 分けてソートして貼り付ける() Dim srcSheet As Worksheet Dim 6 2023/08/04 19:57
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Excel(エクセル) VBAの指示の内容 昨日こちらでご教示頂いたのですが初心者な為、一つ一つの指示が何をやっているのかわ 2 2022/10/25 18:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAでCOPYを繰り返すと、処理が...
-
【マクロ】並び替えの範囲が、...
-
【マクロ】オートフィルター を...
-
Vba Array関数について教えてく...
-
Vba 型が一致しません(エラー1...
-
【ExcelVBA】値を変更しながら...
-
vbsでのwebフォームへの入力制限?
-
エクセルのマクロについて教え...
-
【ExcelVBA】5万行以上のデー...
-
VBAでセルの書式を変えずに文字...
-
【マクロ】開いているブックの...
-
Vba セルの4辺について罫線が有...
-
vb.net(vs2022)のtextboxのデザ...
-
Excel VBA 選択範囲の罫線色の...
-
【マクロ】売上一覧YYYYMMDDHHS...
-
エクセルの改行について
-
VBAで特定の文字が入った行をコ...
-
WindowsのOutlook を VBA から...
-
Excel 範囲指定スクショについ...
-
【マクロ】シートの変数へ入れ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Vba セルの4辺について罫線が有...
-
vbsでのwebフォームへの入力制限?
-
【ExcelVBA】5万行以上のデー...
-
【マクロ】売上一覧YYYYMMDDHHS...
-
【マクロ】開いているブックの...
-
【マクロ】並び替えの範囲が、...
-
エクセルの改行について
-
エクセルのマクロについて教え...
-
vb.net(vs2022)のtextboxのデザ...
-
VBAでCOPYを繰り返すと、処理が...
-
VBA ユーザーフォーム ボタンク...
-
エクセルのVBAコードと数式につ...
-
エクセルのVBAコードについて教...
-
[VB.net] ボタン(Flat)のEnable...
-
【マクロ】変数を使った、文字...
-
改行文字「vbCrLf」とは
-
質問58753 このコードでうまく...
-
【マクロ】シートの変数へ入れ...
-
ワードの図形にマクロを登録で...
-
算術演算子「¥」の意味について
おすすめ情報



いつも書込みありがとうございます。m(_ _"m)
すみません。添付の図が間違っていました。実際には1行目には項目がはいっているため、やはりC2からになります。申し訳ありません。
>myList = Range("C2", Range("C" & Rows.Count).End(xlUp))
不思議なことに4項目のときは問題なく動いているんですが、項目を5つにすると
稼働しなくなるんです。(GからKにはデータはきちんと表示されるのですがそのあとエラーメッセージ「インデックスが有効な範囲にありません)が表示されその後の処理が稼働しない)
デバック内容については添付します。
あと、関数ありがとうございます。処理はできました。
ただデータが大量のため、関数だとファイルがかなり重くなっていました。
何かまたアドバイスがありましたらよろしくお願いします。
理由それだったんですね。B列に項目の文字データはあるのにC列のデータが入ってないから
動かなかったのが判明しました。
その箇所を行を選んで削除したらエラーメッセージでなくなりました。
勉強になりました。
ありがとうございました。