VBA

質問

VBA

申し訳ございませんが行列操作がわかりません。
行数は一定ではありません。
ご意見をいただけませんか?

(1)
Sheet1そして、対象例A, Cは存在します。
↓を実行するとエラーメッセージ"インディクスが有効範囲内にありません。"が表示されます。
理由が分かりません。

Worksheets("Sheet1").Range("A", "C").Copy

(2)
変更前;
(A列)
1 データ１
2
3 データ２
4
.
.

変更後;
(A列)
1 データ１
2 データ１
3 データ２
4 データ２
. データ２
.　データ２

行数は一定ではありません。
ThisWorkbook.SaveAs Filename:="c:\xxxxxxxxx.xls"
Workbooks("xxxxxxxxx.xls").Worksheets("Sheet1").Range("A", "C").Paste
ワークブックを別名保管して新たに作成して、同列に変更値を反映します。
そして、A列には直前のセル値を新しいワークブックに反映してすべてのセルを埋めたいのですが定義が分かりません。

(3)
行数は一定ではありません。
最終行に行を追加したのですが定義が分かりません。

Application.CutCopyMode = False
Range(?????).Insert

(4)
変更前;
(C列)
1 データ３
2　データ３
3 データ３
4　データ３
.　データ３
.　データ３

変更後;
(C列)
1 データ３
2 データ３
3 データ３
4 データ３
.　データ３
.　データ３
. データ２

行数は一定ではありません。
(3)で追加した最終行のセルCにはA列の直前値を挿入したいのですが定義が分かりません。

(5)
行数は一定ではありません。
D列を新しいワークブックに作成します。
(D列)
1 ●
2 ●
3 ●
4 ●
. ●
. ▲

追加した最終行の直前まではすべて●、最終行のセルには▲を挿入したいのですが定義が分かりません。

Application.CutCopyMode = False
Range("D").Insert
?????

回答No.6

mt2008 · ベストアンサー

> Worksheets("バッチ帳票一覧（勘定系）").Range("D:E,K:K,V:X,AF:AM,Y:Y").Copy

↑この様にコピーしても、貼り付けるときには列の並びのまま（D:E,K:K,V:X,Y:Y,AF:AM）ペーストされます。
Y列を最後にしたいなら、Y列だけ別にコピー＆ペーストしてください。

また、Y列の値を処理するのに .Cells(i, 14) を見ていますが、D:E,K:K,V:X,AF:AM,Y:Yだと15列（O列）じゃないでしょうか。

--
> ●は、Sheet3(変更前)Y列に値があるときだけSheet1(変更後)G列にコピーをしたいです。

今のコードのままで良いと思います。ただ、列を14→15にしてください。

--
> V列にはセル結合している箇所がいくつかあり、対象行のすべてのセルGにコピーできません。

一応V列に結合セルがあっても動作するようにしたつもりですが、どんな結合をしているのでしょう？
また、↓は値のみペースト後のセルに対しての処理ですので無意味です。
> .Cells(i, 14).MergeCells = False 'G列のセル結合箇所の削除

お礼

ありがとうございました。
質問がゴチャゴチャしてきましたので再度、質問し直します。

補足

ありがとうございました。
ご意見をいただけませんか?

１．
列移動の件は???.Cutメソッド、???.Insertメソッドで解決できました。
Y列（"Sheet1"のG列）の最後尾列(Q列)へ移動しました。

２．
提示例が悪くて申し訳ございません。
G列に●をコピーする箇所はY列に値がある行だけです。
今回はY列に値があれば、最後に行の追加処理をしてG列に"★"をコピーします。
Y列("Sheet3")はセル結合しているデータです。
すべての対象行にはG列に"●"をコピーしたいです。
現行コードはセル結合を解除すると対象行毎に新規に一行を追加されてG列に"★"がコピーされてしまいます。
ご提供していただいたサンプルですと対象行を１行見て、G列に"●"をコピーして行の追加処理をしてG列に"★"がコピーします。
だだし、他の対象行のG列には"●"がコピーされません。
対象行のG列すべてに"●"がコピーして、行の追加処理をしたいのです。

方法としては、X列("Sheet3")がY列("Sheet3")に値の有無を見てG列に"●"をコピーして、行の追加処理をしたいのです。
また、行の追加処理ですがY列値が連なっているケースがございますので現在処理の対象としているY列は直下の値を見て
値が異なるならば、その直前で行の追加処理をしたいのです。

変更前(Sheet3);
(X列) 　 (Y列)
1 ABC000 テーブル４
2 ABC000 (同上)
3 ABC000 テーブル５
4 ABC000 (同上)
.
.

変更後(Sheet1);
(F列)　 (G列)
1 ABC000 　 ●
2 ABC000 　 ●
3 テーブル４ ★
4 ABC000 　 ●
5 ABC000 　 ●
6 テーブル５ ★
.
.

-------

コード;

Option Explicit

Sub Sample()
Dim nRow, i

Worksheets("Sheet3").Range("D:E,K:K,V:Y,AF:AM").Copy
With Worksheets("Sheet1")
'Y列も"Sheet1"にコピー（G列）
.Range("A1").PasteSpecial Paste:=xlPasteValues
nRow = .Cells(Rows.Count, 1).End(xlUp).Row 'データのある最終行
'最初にブランクを埋める（埋めるのは5行目から）
For i = 5 To nRow
If .Cells(i, 2) = "" Then .Cells(i, 2) = .Cells(i - 1, 2) 'B列
If .Cells(i, 4) = "" Then .Cells(i, 4) = .Cells(i - 1, 4) 'D列
If .Cells(i, 7) = "" Then .Cells(i, 7) = .Cells(i - 1, 7) 'G列
Next i

'Y列（"Sheet1"のG列）のデータ分行を追加
For i = nRow To 4 Step -1
'Y列（"Sheet1"のG列）にデータがあるか
If .Cells(i, 7) <> "" Then
.Rows(i).Copy
.Rows(i).Insert
.Cells(i + 1, 3) = "-"
.Cells(i + 1, 6) = .Cells(i, 7)
.Cells(i, 7) = "●"
.Cells(i + 1, 7) = "★"
End If
Next i
'Y列（"Sheet1"のG列）の最後尾列(Q列)への移動
.Range("G:G").Cut
.Range("Q:Q").Insert
End With
End Sub

回答No.5

mt2008

こんな感じですかね。
あとはご自身で手を加えて下さい。

Sub Sample()
　　Dim nRow, i
　　Sheets("Sheet3").Range("D:E,K:K,V:Y").Copy
　　With Sheets("Sheet1")
　　　　'Y列もSheet1にコピー（G列）
　　　　.Range("A1").PasteSpecial Paste:=xlPasteValues
　　　　nRow = .Cells(Rows.Count, 1).End(xlUp).Row 'データのある最終行
　　　　'最初にブランクを埋める（埋めるのは5行目から）
　　　　For i = 5 To nRow
　　　　　　If .Cells(i, 2) = "" Then .Cells(i, 2) = .Cells(i - 1, 2) 'B列
　　　　　　If .Cells(i, 4) = "" Then .Cells(i, 4) = .Cells(i - 1, 4) 'D列
　　　　Next i

　　　　'Y列（Sheet1のG列）のデータ分行を追加
　　　　For i = nRow To 4 Step -1
　　　　　　'Y列（Sheet1のG列）にデータがあるか
　　　　　　If .Cells(i, 7) <> "" Then
　　　　　　　　.Rows(i).Copy
　　　　　　　　.Rows(i).Insert
　　　　　　　　.Cells(i + 1, 3) = "―"
　　　　　　　　.Cells(i + 1, 6) = .Cells(i, 7)
　　　　　　　　.Cells(i + 1, 7) = "★"
　　　　　　End If
　　　　　　.Cells(i, 7) = "●"
　　　　Next i
　　End With
End Sub

補足

サンプルをありがとうございます。
ご意見をいただけませんか?

(1)
●は、Sheet3(変更前)Y列に値があるときだけSheet1(変更後)G列にコピーをしたいです。
Y列に値がないときはブランクにします。(混乱させて申し訳ございません。)
V列にはセル結合している箇所がいくつかあり、対象行のすべてのセルGにコピーできません。
↓定義を追加しましたがうまくいきません。
.Cells(i, 14).MergeCells = False 'G列のセル結合箇所の削除
出来る限るオリジナル書式を変更したくありません。

(2)
AM-AF列を追加しました。
AM-AF間のセル情報はコピーするだけでセルに値がなくても構いません。
Gの結果を最後列へ追加したいのですがうまくいきません。

-------

コード;

Option Explicit

Sub Sample()
Dim nRow, i

'Worksheets("バッチ帳票一覧（勘定系）").Range("D:E,K:K,V:Y").Copy
Worksheets("バッチ帳票一覧（勘定系）").Range("D:E,K:K,V:X,AF:AM,Y:Y").Copy
With Worksheets("編集後")
'Y列も"編集後"にコピー（G列）
.Range("A1").PasteSpecial Paste:=xlPasteValues
nRow = .Cells(Rows.Count, 1).End(xlUp).Row 'データのある最終行
'最初にブランクを埋める（埋めるのは5行目から）
For i = 5 To nRow
If .Cells(i, 2) = "" Then .Cells(i, 2) = .Cells(i - 1, 2) 'B列
If .Cells(i, 4) = "" Then .Cells(i, 4) = .Cells(i - 1, 4) 'D列
Next i

'Y列（"編集後"のG列）のデータ分行を追加
For i = nRow To 4 Step -1
'Y列（"編集後"のG列）にデータがあるか
If .Cells(i, 14) <> "" Then
.Rows(i).Copy
.Rows(i).Insert
.Cells(i + 1, 3) = "-"
.Cells(i + 1, 6) = .Cells(i, 14)
.Cells(i, 14).MergeCells = False
.Cells(i, 14) = "●"
.Cells(i + 1, 14) = "★"
End If
Next i
End With
End Sub

回答No.4

mt2008

補足願います。

・変更前(Sheet3)D列から、変更後(Sheet1)A列への変換ルールが解りません。
　どういうルールで「ABCDEF」と「123456」が、「ABC123」になるのでしょう。

・変更前(Sheet3)Y列のデータは、変更前(Sheet3)D列のデータ1種類毎に1データのみという事で良いのですか。
＃「ABCDEF」に「テーブル4」、「123456」に「テーブル５」

・変更後(Sheet1)D列最後の「－」は無条件で付ければ良いのですか。

補足

ご返信ありがとうございます。
ご意見をいただけませんか?

(1)
混乱させて申し訳ございません。

■行を追加するルールですが、変更前(Sheet3)のY列に値がある度に変更前(Sheet1)に行を追加します。
Y値がブランクのときは追加しません。

■D→A列へはセル値をコピーするだけです。
　タイポでした。

■変更後(Sheet1)、追加行のC列には"-"を無条件で挿入します。
　追加行のF列には変更後(Sheet1), 直前Y値
　追加行のG列には無条件で"★"を無条件で挿入します。

実施したいことは以下のことです。

変更前(Sheet3);
(D列) (E列) (K列) (V列) (W列) (X列) (Y列)
1 ABCDEF テーブル１ ABC456 テーブル２　 ABC789 ABC000 (ブランク)
2 ABCDEF (ブランク) ABC456 (ブランク) ABC789 ABC000 テーブル４
3 123456 (ブランク) ABC456　テーブル３ ABC789 ABC000 テーブル５
4 123456 (ブランク) ABC456 (ブランク) ABC789 ABC000 (ブランク)
.
.

変更後(Sheet1); 例では追加は3,4行目です。
(A列) (B列) 　 (C列) (D列) 　 (E列) (F列) 　(G列)
1 ABCDEF テーブル１ ABC456 テーブル２　 ABC789 ABC000 　 ●
2 ABCDEF テーブル１ ABC456 テーブル２ ABC789 ABC000 　 ●
3 ABCDEF テーブル１ 　-　 テーブル２ ABC789 テーブル４ ★ 　
4 123456 テーブル１ ABC456　テーブル３ ABC789 ABC000　　 ●
5 123456 テーブル１ - 　 テーブル３ ABC789 テーブル５ ★
6 123456 テーブル１ ABC456 テーブル３ ABC789 ABC000 　 ●
.
.

(2)
V列にはセル結合している箇所がいくつかあり、D列へ毎行コピーできていまでした。
↓定義を追加しましたがうまくいきません。
Worksheets("Sheet3").Range("V1:V65535").End(xlUp).MergeCells = False 'V列のセル結合箇所の削除
出来る限る書式を変更したくありません。

(3)
先頭行1-3はヘッダーになります。
追加した行のG列以外で, 4行目のG列から○を埋めていきたいのですが↓定義ではうまくいきません。
.Cells(4, 7) = "○" '4行目のG列は必ず○

-------

コード;
Option Explicit

Dim i As Integer, j As Integer, nRow As Integer, vData

Sub Sample()
Worksheets("Sheet3").Range("D:E,K:K,V:Y").Copy
Worksheets("Sheet3").Range("V1:V65536").MergeCells = False 'V列のセル結合箇所の削除

With Worksheets("Sheet1")
.Range("A1").PasteSpecial
nRow = .Cells(Rows.Count, 1).End(xlUp).Row 'データのある最終行
.Cells(4, 7) = "●" '4行目のG列は必ず●

'条件文で、Y値があればvDataにコピーしたいのですが方法が分かりません。
For i = 2 To (nRow + 1) '最終行+1行まで処理することで最終行の後に1行追加
For j = 1 To 6 'A～F列
If .Cells(i, j) = "" Then .Cells(i, j) = .Cells(i - 1, j)
Next j
'追加した最終行の処理
.Cells(nRow + 1, 3) = "-"
.Cells(nRow + 1, 6) = vData
.Cells(nRow + 1, 7) = "▲"
.Cells(i, 7) = "●"
Next i

End With
End Sub
--------------------

回答No.3

mt2008

ANo.2です。

Sheet3、Y1セルの「テーブル4」の扱い。Sheet1のC5セルになぜ「－」なのか。
以上の2点が不明ですが、変更前を変更後の形にするマクロを作りました。
後はご自分で修正してみてください。
Sheet3のデータ（セルY1のデータ以外）をSheet1に張り付けてから形を整えています。

Sub Sample()
　　vData = Sheets("Sheet3").Range("Y1") 'Sheet3 Y1のデータを変数に代入
　　Sheets("Sheet3").Range("D:E,K:K,V:X").Copy
　　With Sheets("Sheet1")
　　　　.Range("A1").PasteSpecial
　　　　nRow = .Cells(Rows.Count, 1).End(xlUp).Row 'データのある最終行
　　　　.Cells(1, 7) = "●" '1行目のG列は必ず●
　　　　For i = 2 To (nRow + 1) '最終行+1行まで処理することで最終行の後に1行追加
　　　　　　For j = 1 To 6 'A～F列
　　　　　　　　If .Cells(i, j) = "" Then .Cells(i, j) = .Cells(i - 1, j)
　　　　　　Next j
　　　　　　.Cells(i, 7) = "●"
　　　　Next i
　　　　'追加した最終行の処理
　　　　.Cells(nRow + 1, 3) = "―"
　　　　.Cells(nRow + 1, 6) = vData
　　　　.Cells(nRow + 1, 7) = "★"
　　End With
End Sub

補足

サンプルをありがとうございました。
ご質問がございます。
ご意見をいただけませんか?

(1)
V列 → D列へのコピー;
E列 → B列への毎行コピーはできています。
最直前の値をコピーできていません。

(2)
申し訳ございません。
D列単位で1行追加したいです。
方法としては、A列ループ処理の中にB-G列のループ処理を定義すれば良いでしょうか?

変更前(Sheet3);
(D列) (E列) (K列) (V列) (W列) (X列) (Y列)
1 ABCDEF テーブル１ ABC456 テーブル２　 ABC789 ABC000 テーブル４
2 ABCDEF (ブランク) ABC456 (ブランク) ABC789 ABC000 (ブランク)
3 123456 (ブランク) ABC456　テーブル３ ABC789 ABC000 テーブル５
4 123456 (ブランク) ABC456 (ブランク) ABC789 ABC000 (ブランク)

変更後(Sheet1);
(A列) (B列) 　 (C列) (D列) 　 (E列) (F列) 　(G列)
1 ABC123 テーブル１ ABC456 テーブル２　 ABC789 ABC000 　 ●
2 ABC123 テーブル１ ABC456 テーブル２ ABC789 ABC000 　 ●
3 ABC123 テーブル１ ABC456 テーブル２ ABC789 テーブル４ ★ 　
4 ABC123 テーブル１ ABC456　テーブル３ ABC789 ABC000　　 ●
5 ABC123 テーブル１ ABC456 テーブル３ ABC789 ABC000 　 ●
6 ABC123 テーブル１ - 　 テーブル３ ABC789 テーブル５ ★

回答No.2

mt2008

(1)
> ↓を実行するとエラーメッセージ"インディクスが有効範囲内にありません。"が表示されます。
> 理由が分かりません。
>
> Worksheets("Sheet1").Range("A", "C").Copy

定義が違います。
Worksheets("Sheet1").Range("A:A,C:C").Copy

（2）
A列の全てって、104万8576行までですか？
以下の様なコードでA列を埋めるしかないと思います。
Sub Macro2()
　　For i = 2 To Rows.Count
　　　　If Range("A" & i) = "" Then Range("A" & i) = Range("A" & i - 1)
　　Next i
End Sub

本当に必要ですか？

（3）
> 最終行に行を追加したのですが定義が分かりません。

A列にデータがある最終行なら↓
nRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(nRow + 1).Insert Shift:=xlDown

（4）
> (3)で追加した最終行のセルCにはA列の直前値を挿入したいのですが定義が分かりません。

nRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(nRow + 1).Insert Shift:=xlDown
Range("C" & nRow + 1) = Range("A" & nRow)

(5)
> 追加した最終行の直前まではすべて●、最終行のセルには▲を挿入したいのですが定義が分かりません。

やりたいことが良く見えないですが、こういう事かな？
nRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("D1:D" & (nRow - 1)) = "●"
Range("D" & nRow) = "▲"

もう少し、何をやりたいのか整理した方が良いように思われます。

補足

ありがとうございました。
実施したいことは、↓のような事です。
行数は一定ではありません。
教えていただいたループ回数、"For i = 2 To Rows.Count"ではオーバーフローでした。
ご意見をいただけませんか?
質問は赤字箇所です。

変更前(Sheet3);
(D列) (E列) (K列) (V列) 　(W列) (X列) (Y列)
1 ABC123 テーブル１ ABC456 テーブル２　ABC789 ABC000 テーブル４
2 ABC123 ABC456 ABC789 ABC000
3 ABC123 ABC456　テーブル３ ABC789 ABC000
4 ABC123 ABC456 ABC789 ABC000

変更後(Sheet1);
(A列) (B列) 　(C列) (D列) 　(E列) (F列) (G列)
1 ABC123 テーブル１ ABC456 テーブル２　ABC789 ABC000 ●
2 ABC123 テーブル１ ABC456 テーブル２ ABC789 ABC000 ●
3 ABC123 テーブル１ ABC456　 テーブル３ ABC789 ABC000　 ●
4 ABC123 テーブル１ ABC456 テーブル３ ABC789 ABC000 ●
5 ABC123 テーブル１ - 　 テーブル３ ABC789 テーブル４ ★　←　最終行追加

行数は一定ではありません。
教えていただいたループ回数、"For i = 2 To Rows.Count"ではオーバーフローでした。
ご意見をいただけませんか?
質問は＞箇所です。

Option Explicit

Dim i As Integer
Dim nRow As Integer

Sub prcCopyPasteColumns()

'D → A列
Worksheets("Sheet3").Range("D:D").Copy
Worksheets("Sheet1").Range("A:A").Insert
＞コピー元、コピー先の列番号が異なっていました。

'E → B列
Worksheets("Sheet3").Range("K:K").Copy
For i = 2 To Range("A65535").End(xlUp)
If Range("K" & i) = "" Then Range("K" & i) = Range("K" & i - 1)
Worksheets("Sheet1").Range("B:B").Insert
Next i
＞無現ループしてしまいます。

'K → C列
Worksheets("Sheet3").Range("K:K").Copy
For i = 2 To Range("A65535").End(xlUp)
If Range("K" & i) = "" Then Range("K" & i) = Range("K" & i - 1)
Worksheets("Sheet1").Range("C:C").Insert
Next i

'V → D列
Worksheets("Sheet3").Range("V:V").Copy
Worksheets("Sheet1").Range("D:D").Insert

'W → E列
Worksheets("Sheet3").Range("W:W").Copy
For i = 2 To Range("A65535").End(xlUp)
If Range("W" & i) = "" Then Range("W" & i) = Range("W" & i - 1)
Worksheets("Sheet1").Range("E:E").Insert
Next i

'X → F列
Worksheets("Sheet3").Range("X:X").Copy
Worksheets("Sheet1").Range("F:F").Insert

'最終行
nRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(nRow + 1).Insert Shift:=xlDown
Range("C" & nRow + 1) = "-" '"-" → Cセル
Worksheets("Sheet3").Range("Y:Y").Copy
Range("F" & nRow + 1) = Range("Y" & nRow).Insert 'Y直前値 → Fセル

'G列
nRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("G1:G" & (nRow - 1)) = "●" '"最終行-1"まで
Range("G" & nRow) = "▲" '最終行

End Sub

回答No.1

FEX2053

Worksheets("Sheet1").Range("A", "C").Copy

これ、単なる書式の間違い。

Worksheets("Sheet1").Range("A:A","C:C").Copy

こっちが正当。範囲がある時は、記述も範囲にしないと。

最終行の選択は

Range("A65535").End(Xlup).select

で行けるはず。要は「十分後ろのセルを選んでおいて、
[Ctrl]+[↑]で飛ばす」という処理をするってことです。

あいているところにデータを一気に埋めるのは、どっちかというと
マクロで頑張るより、この手の関数でデータを埋めた列を代わりに
使うか、値コピーで上書きしちゃった方が正解かと思います。

=IF(A2="",A1,A2)