複数選択状態のセルを可変範囲分横にドラッグするVBA

質問

エクセル2003のVBAの記述について教えてください。

＜状態＞2行目にB3から値が入っている。範囲は可変。
＜作業＞2つずつの合計を3行目に算出する。
＜VBAで記述したい内容＞
１　C3に「=B2+C2」と入力する
２　B3とC3を選択する
３　２の状態で2行目の値が入っている最後の列のセルまでドラッグする
-----
これをVBAで記述するにあたって以前教えていただいたこと（http://questionbox.jp.msn.com/qa4286686.html）を参考に自分なりに考えて作成したのですが、やっぱり３の部分でエラーになってしまいます。（平たく言うと横方向へのドラッグの記述方法が判らない）

どこか基本的な部分がトンチンカンなのは判りますが、色々検索＆試してもどこがどうトンチンカンなのか判りません。

Range("C3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[-1]+R[-1]C"
Range("B3:C3").Select
Selection.AutoFill Destination:=Range("B3:3" & Cells(2,
Columns.Count).End(xlToLeft).Column), Type:=xlFillDefault

上手く動くようご指摘、よろしくお願いします。

回答No.4

fujillin · ベストアンサー

すでにことは足りているようですが、参考までに・・

作業ではオートフィルは当然の方法ですが、VBAの思考法としては必ずしもベストの方法とはならない場合もあります。
また、人間の作業だと当り前にチェックしていることも、マクロだとそのまま実行してしまいます。例えば、C2セルのゴミ（何かの値）が入っていたとすると、全部コピーされてしまったり、データの数が減る（ことはないのかも知れませんが）場合は、古い式が残ったままなので、その部分に0や#VALUE!が表示されてしまいます。
これを回避するには、最初に第3行目をクリアしておくと良いでしょう。例えば、
Sub tes()
　col = Cells(2, Columns.Count).End(xlToLeft).Column　'//←入力最後の列番号
　col = col + 1 - (col Mod 2)　　'//←列番号が偶数だったら＋１
　Rows(3).ClearContents　　'//←3行目のクリア
　For i = 3 To col Step 2　　'//←C3から1列おきに式を代入
　　Cells(3, i).FormulaR1C1 = "=R[-1]C[-1]+R[-1]C"
　Next i
End Sub
xlToLeftで得た列をそのまま使用していないのは、偶数列までデータがあった場合（ペアの片方までなので、これもあり得ないこなのとかも知れませんが）も計算式を代入するように調整をしています。

さらに言わせていただくと、式を代入していますが、どうせマクロを走らせて最終結果を得るのなら、計算もマクロでやらせて値を代入すれば良いだけでは？（式を記憶しない分、エクセルの容量が軽くなります＝まぁ、たいした量ではありませんが）
これに関しては、「計算式をいれておいたほうが値を変更した時に連動して変化してくれるので便利」というご意見があるでしょうが、それならマクロなど利用せずに、最初から式をいれておくという考えの方が良さそうです。
そうすれば、マクロを実行するという手間が不要になります。多分、「0」が表示されるのが嫌なので、「入力されているところまで」となっているのでしょうが、
　C3：　=IF(AND(B2="",C2=""),"",B2+C2)
としておいて、必要そうな列まであらかじめオートフィルしておけば、未入力の場合は表示されず、入力した時点で（マクロを実行しなくても）即座に計算値が表示されることになります。

さて、これまでは入力されたデータが常に数字または未入力という暗黙の仮定がありましたが、そうでない場合（abcなどの文字がある場合）は結果は#VALUE!になってしまいます。
これはこれで、入力値がおかしいという表示なので意味はあるのですが、正しい値の場合のみ表示するという式にするなら、
　C3：　=IF(AND(B2="",C2=""),"",IF(ISERROR(B2+C2),"",B2+C2))
となります。（少々長くなってしまいますが）
この式では、文字と数字の組合せの場合は計算不能と考えて何も表示されません。しかし、「片方でも数字があれば（文字は無視して）計算する」というルールにするなら
　C3：　=IF(OR(ISNUMBER(B2),ISNUMBER(C2)),SUM(B2:C2),"")
ということも可能です。

要求もされていないことまでを、差し出がましくも長々と書きまして、大変失礼いたしました。
考え方のご参考まで。

お礼

色々と丁寧にご指導いただき、大変有難うございました。
もっともっと勉強していきたいと思います。

回答No.3

mitarashi

A No.1さんに一票
折角FormulaR1C1を使っているのだから、
Sub test()
Range(Range("c2"), Range("c2").End(xlToRight)).Offset(1, 0).FormulaR1C1 = "=R[-1]C[-1]+R[-1]C"
End Sub
で済んでしまいませんか？
外していたらすみません。

お礼

重ね書きですみません。

ありがとうございました。教えていただいたことを元に自分なりに工夫して行こうと思います。

補足

アドバイスありがとうございます。

この記述ですと、C2から最後まで連続して計算式が入力されることになりました。
当方の目指す最終形は、3行目のC3、E3、G3、I3、K3、M3・・・と一つおきにそれぞれの計算式が入力されている形なのです。

回答No.2

xls88

>Destination:=Range("B3:3" & Cells(2, Columns.Count).End(xlToLeft).Column)
気持はわかりますが、上記で
Cells(2, Columns.Count).End(xlToLeft).Column)
の戻り値は列番号になります。
仮に8が返されたとして
Range("B3:38")
と記述していることになります。
これはおかしいですね、成立していませんね。

Selection.AutoFill _
　　Destination:=Range("B3", Cells(2, Columns.Count).End(xlToLeft).Offset(1, 0)), _
　　Type:=xlFillDefault
あるいは
Selection.AutoFill _
　　Destination:=Range("B3", Cells(3, Cells(2, Columns.Count).End(xlToLeft).Column)), _
　　Type:=xlFillDefault
を試してみてください

>Range("B3:C3").Select
これっておかしくないですか？

お礼

ここの使い方が馴れて無いようで、アチコチに書きこむ形になってしまって申し訳ありません。

重ねて、ありがとうございました。

補足

教えていただいた記述でキレイサッパリ解決できました。
ありがとうございました。

>仮に8が返されたとして
>Range("B3:38")
>と記述していることになります。

本当は、例えばRange("B3:AJ3")などと、列のローマ字とか最後のセル番地が入るようにしたかったのです。

>>Range("B3:C3").Select
>これっておかしくないですか？

すみません、どうおかしいのか良く判りません。
狙いは、空白のB3と式の入ったC3を選択して、その位置関係のまま横にドラッグさせようと思ったからなんですけども。。

回答No.1

hige_082

根本的に考えを変えてはいかが
式の入っているセルを値の入っている所までフィルするのではなく
２の状態で2行目に値が入っているかどうかを判断し、入っていれば式を書き込むように変更しては
後々応用も出来ますよ

お礼

お礼はこちらでしたね。
間違いました。申し訳ないです。

でも本当に有難うございました。

補足

早速のご回答ありがとうございます。
なるほど。入力されているデータの状況に応じて、効率良く手を加えていくという方法ですね。ANo.3の方とご意見と合わせて考えてみたいと思います。

ありがとうございました。