エクセルVBA の変数を使うべきでしょうか？

質問

はじめまして。エクセル初心者です。
書籍やサイトで勉強させてもらっていますが、VBAがなかなか難しくてすぐに壁にぶつかってしまいます。少々困ってしまい、詳しい方のアドバイスを頂ければと質問を投稿させていただきました。
どうか宜しくお願い致します。質問ですが、

以下のようなコードで、sheet5のB列の任意のセルをダブルクリックした場合、sheet5のBCD列の同じ行のセル値がsheet1の指定した列に入力されるという処理を作りました。

これで一応目的の動作はするのですが、数が増えると「コンパイルエラー・プロシージャが大きすぎます」というメッセージがでてしまいます。列や行には規則性があるので、もしかしたら変数というものを使ってコードを書き直せばいいのかなと思いネットで調べてみたのですが、今のところさっぱり理解できません。

申し訳ありませんが、分かりやすくご教授いただけないでしょうか。バージョンは2003を使っています。

また、下のコードですと、sheet5のBCDいずれかのセルに空白があった場合、sheet1の列に入力されるときに入力される行がずれてしまいます。今は空白を何かで埋めて対処しているのですが、この問題の解決策も教えて頂けると助かります。どうか宜しくお願い致します。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, cancel As Boolean)

If Target.Address = "$B$2" Then

Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B2")
Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C2")
Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D2")
Worksheets("sheet1").Activate
cancel = True

End If

If Target.Address = "$B$3" Then

Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B3")
Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C3")
Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D3")
Worksheets("sheet1").Activate
cancel = True

End If

If Target.Address = "$B$4" Then

Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B4")
Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C4")
Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D4")
Worksheets("sheet1").Activate
cancel = True

End If

　　・　　
　　・
　　・　　
　　・
　　・　　
　　・

End Sub

回答No.1

keithin · ベストアンサー

わかりやすく答えだけ教えてもらえればよいですね？
まぁでも，変数は使わなくても書けますが，変数を使った方がもっとわかりやすくなります。

手順：
今のマクロをきれいに全部消す
改めて下記をコピー貼り付ける

private sub Worksheet_BeforeDoubleClick(byval Target as excel.range, cancel as boolean)
　dim r as long
　if target.column <> 2 then exit sub
　cancel =true

　r = worksheets("Sheet1").range("B65536").end(xlup).offset(1).row

　worksheets("Sheet1").cells(r, "B").value = iif(target="","'",target.value)
　worksheets("Sheet1").cells(r, "H").value = cells(target.row, "B").value
　worksheets("Sheet1").cells(r, "K").value = cells(target.row, "C").value
　worksheets("Sheet1").cells(r, "M").value = cells(target.row, "D").value

'worksheets("Sheet1").select
end sub

＞空白があった場合

どういう結果を残したいかに応じて，工夫代はいろいろ考えられます。
上記なんかは割と安直な解決策をとっていますが，ほかにも「変数ｒに取得すべきシート１の最後の行＋１」をまじめに手抜きせず対象列すべてを丁寧に調べて，その一番大きい値（行）に決めるといった手も取れますね。

あるいはシート１の具体的な内容によっては，「シート１の最後のセルの行＋１」とかで取得する手も使えるかもしれません。

＃参考
エクセルの「シートの最後のセル」とは，ワークシート上でCtrl+Endキーを押してジャンプする操作のことをいいます。
「新しいマクロの記録」で記録マクロをとって，この操作がマクロではどういう記述になるかしっかり勉強してください。

お礼

ここまで丁寧に教えて頂けるとは思っていませんでした。
ご親切に感謝致します。
教えて頂いたコードであっさり問題が解決しました。
「シートの最後のセル」の解説もありがとうございます。
似たような処理をする必要がこれからも出てきそうなので、教えて頂いたコードを理解できるよう勉強します。
いつかは私も教えてあげられる側になりたいと思います。
本当にありがとうございました。

回答No.2

hallo-2007

うわ～
行の数だけ、コードを準備するつもりだったのでしょうか。
If Target.Column = 2 Then

Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B" & Target.Row)
・・・
で考え直してみてください

お礼

はい、行の数だけコードを準備するつもりでした。
といいますか、行の数だけコードを書いたらエラーがでました。
非常にアホなやり方なんだろうとは思っていたのですが、今の自分のレベルではそれが精一杯でして・・
親切に教えていただいて本当に助かりました。感謝致します。
同じ処理でも色んな方法があるんだなと勉強になりました。
ありがとうございました。