ExcelのVBAで一部困っています。

質問

現在下記のようなプログラム（一部）を使っています。
数式をコピーしているのですが、これを左側のセルが空白になったらコピーをやめるか、数式記入をやめる方法に変えたいのですが、どなたか、ご教授ください。
後で余計にコピーしたセルを消す作業を低減したいと思っています。
宜しくお願いします。

Range("E1").Select
ActiveCell.FormulaR1C1 = "専用コード"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""ホンテン "",""ラ001"",IF(RC[-1]=""エーテン "",""ラ005"",IF(RC[-1]=""ビーテン "",""ラ007"",IF(RC[-1]=""シーテン "",""ラ008"",IF(RC[-1]=""ディーテン "",""ラ009"",IF(RC[-1]=""イーテン "",""ラ015"",IF(RC[-1]=""エフテン "",""ラ011"",IF(RC[-1]=""ジーテン "",""ラ014"",""""))))))))"
Range("E2").Select
Selection.Copy
Range("e3:e1500").Select

回答No.2

fujillin · ベストアンサー

ご質問のコードが途中で切れてしまっていますが、そのあとにＥ2の内容をpasteしているものと推測して…

普通にループでやるなら
　For Each c In Range("E2:E" & Cells(Rows.Count, 4).End(xlUp).Row
　　c.FormulaR1C1 = Range("E2").FormulaR1C1
　Next c

エクセルのオートフィルでやるなら
　Range("E2").AutoFill Destination:=Range("E2:E" & Cells(Rows.Count, 4).End(xlUp).Row)

みたいな感じ。
ちなみに「D列が空白だったら」ではなく「D列の最終行まで」という形で記しています。
「D列が空白だったら」をそのまま記述するなら、
　Do While～　や Do Untill～ などになろうかと思います。

お礼

早速にありがとうござました。
試してみたところ、うまくいきました。
助かりました。
本当にありがとうございました。

回答No.3

noname#187541

こんにちは。

数式をコピー(正確にはセルすべてですね)はしていますが、貼り付け(ペースト)の記述がないですね。
E3:E1500を範囲選択してコードが終わっていますが、この範囲に貼り付けると言うことでしょうか。
この範囲の左隣のセルが空白になったらコピーをやめる、逆に言うと空白になるまでが貼り付ける範囲と言うことですね。

それから、SelectしてActiveCellにしてから処理しているようですが、このような処理は無駄です。Selectしないで直接処理しましょう。

と言うことで、こんな感じでしょうか。

Dim myRow As Long
Range("E1").FormulaR1C1 = "専用コード"
Range("E2").FormulaR1C1 = _
"=IF(RC[-1]=""ホンテン "",""ラ001"",IF(RC[-1]=""エーテン "",""ラ005"",IF(RC[-1]=""ビーテン "",""ラ007"",IF(RC[-1]=""シーテン "",""ラ008"",IF(RC[-1]=""ディーテン "",""ラ009"",IF(RC[-1]=""イーテン "",""ラ015"",IF(RC[-1]=""エフテン "",""ラ011"",IF(RC[-1]=""ジーテン "",""ラ014"",""""))))))))"
myRow = Range("D3").End(xlDown).Row
Range("E2").Copy Destination:=Range("E3:E" & myRow)
Range("E" & myRow + 1 & ":E1500").ClearContents

お礼

ご回答ありがとうございます。
＞それから、SelectしてActiveCellにしてから処理しているようですが、このような処理は無駄です。Selectしないで直接処理しましょう。
ご指摘、ごもっともです。
ありがとうございました。
大変参考になりました。
本当にありがとうございました。

回答No.1

cj_mover

Sub test()
Cells.CurrentRegion.Columns(5).Formula _
　= "=INDEX(" _
　& "{"""";""ラ001"";""ラ005"";""ラ007"";""ラ008"";""ラ009"";""ラ015"";""ラ011"";""ラ014""}" _
　& ",SUMPRODUCT(" _
　& "COUNTIF(D1," _
　& "{""ホンテン "";""エーテン "";""ビーテン "";""シーテン "";""ディーテン "";""イーテン "";""エフテン "";""ジーテン ""}" _
　& ")*ROW($1:$8))+1)"
End Sub

１）　CurrentRegion については、ヘルプで確認してください。
　　こんな時に使う為のものです。
　　本来、テーブル形式になっているExcelシートは
　　「表は必ず、空白行と空白列に囲まれている。」
　　「表の中には、空白行と空白列を作らない。」
　　というルールで作られるべきものなので、
　　適合していれば扱いが楽になる好例です。
２）　例示された数式で、"ホンテン "など、後ろに半角スペースが付いていますので、
　　そのまま反映させました。
３）　ＶＢＡに関するご質問ですが、例示された数式は、Excel2007以降のバージョンでしか使えないので、
　　汎用式になおしました。
　　例示された式の右辺エクセル数式の　RC[-1]=　を、D1=　に置換すれば、原型の数式でもいける筈です。
４）　Excel数式レベルの話ですが、数式の中にリストを組み込むのは、あまり感心しません。
　　セル範囲に作ったリスト（テーブル）を、セル範囲として参照する方が、
　　数式も組み易く、より高度に応用でき、ファイルも軽く済みます。
５）　関連の質問も拝見しました。
　　私ならどうするか、真面目に考えました。
　　お求めになっているものが、「何を持って良しとする」のかが、
　　把握できていないので、期待に添えなかったらスミマセン。
６）　私はExcel数式あまり得意じゃありません。

お礼

早速にありがとうございました。
大変参考になりましたよ。
＞６）　私はExcel数式あまり得意じゃありません。
にも関わらず、的確なアドバイスありがとうございました。