VBA、このようなケースはどうしたらよいでしょうか？

質問

すみません。VBA初心者なので質問がちぐはぐになるかと思いますが、よろしく
お願いいたします。
Sub セル番地（）
Worksheets("Sheet1").Select
Cells(2, 2).Select
Range(Selection, Selection.End(xlDown)).Select
rnum = Selection.Rows.Count - 1
MsgBox rnum
End sub
上のVBAでシート１に数字入力されているセルの数を判断します。

Sub sheetcopy()
For X = 3 To 100

If Cells(X, 2) <> "" Then
Cells(X, 2).Copy Destination:=Worksheets("Sheet1").Cells(X + rnum, 2)
ElseIf Cells(X, 2) = "" Then
Exit For
End If

Next
End sub
このVBAは、選択しているシートに入っている数字をシート１へコピーするものです。
”セル番地”で判定した変数rnumを、”sheetcopy”に記載されている変数rnum
へ渡すためにはどのようにしたらよいでしょうか？
引数付きプロシージャの説明を読んでみたのですが、いまいちわかりませんでした。最終的には、各シートに入力されている数値をシート１のあいているセルへどんどんコピーさせたいのです。ひとつのプロシージャに書けば、問題なく動くのですが、見た目をすっきりさせたいと思います。
イメージとしてはこんな感じです。
sub sheetcomplete ()
call セル番地
Worksheets("Sheet2").Select
call sheetcopy

call セル番地
Worksheets("Sheet3").Select
call sheetcopy
～
～
End sub
そもそも、よくわかっていないので滑稽に見えると思いますが、よろしくご指導ください。

回答No.5

imogasi · ベストアンサー

＃４の補足について。
CurrentRegionプロパティについて
(1)a=Range(x).CurrentRegion.Rows.Countはデータ入力済みのセルの塊について、塊の行数を数える。塊とは上下左右スペースがあるとそこで途切れると考える。そして長方形区画を考える。したがって「データ入力済みのセルの塊」が確実な中のセルを、Ｘとして指定しないと、変なことになるのです。Range()は先頭左上セルだけを注目するようである。Range("b2:b100")は　　　Range("b2")と同じと思う。
(2)ただ不思議にa=Range("b2").CurrentRegion.Rows.CountはＢ１に１Ｂ２がスペースとなっていると２となる。同じくＢ１：Ｂ５がスペースでＢ６：Ｂ８に数字が入っているとa=Range("b5").CurrentRegion.Rows.Countは４となる。
(3)Ｂ３：Ｂ４がスペースでa=Range("b3").CurrentRegion.Rows.Countは１となる。
（注）「スペース」とはそのセルでＤＥＬキーを押した状態を指す。本当の未入力も含む。
他のご解答の方はあまりCurrentRegionを使われないようです。私の仕事上で支障が出たことはないが。Ｕｓｅｄｒａｎｇｅ、Ｅｎｄ（ｘｌＤｏｗｎ）などの差をご研究してください。

回答No.4

imogasi

質問のコーディングは（１）マクロの記録臭がする、Select
の多用である。（２）もうひとつセル間でＶａｌｕｅを移すのはＣｏｐｙでなく、（Ｓｈｅｅｔが異なっていても＝（代入）
で行える。これも操作のマクロの記録からのものでしょう。Ｃａｌｌなんて次段階の事項を使わずとも簡単になります。
Sub test01()
a=Worksheets("sheet1").Range("b2:b100").CurrentRegion.Rows.Count
i = a + 1
For x = 3 To 100
If Activesheet.Cells(x, 2) = "" Then
Exit For
Else
Worksheets("sheet1").Cells(i, 2) = ActiveSheet.Cells(x, 2)
i = i + 1
End If
Next x
End Sub
このプログラムだと、同じシート上にカーソルを置いたまま2回以上実行すると累積されるのでご注意。

お礼

いろいろなご指摘ありがとうございます。
上記 プログラムは、とてもすっきりしていて、みていて気持ちが良いです。
参考にさせて頂きました。

補足

Worksheets("sheet1").Range("b2:b100").CurrentRegion.Rows.Count
CurrentRegion.Rows.Count ですが、
Range("b2:b100")の範囲に、10個入力されているセルがあると１０を返してきます。
しかしB1のセルに数値が入力されていると、Range("b2:b100")で範囲指定している
にもかかわらず、１１と数字を返してきてしまいます。

これは、しょうがない現象なのでしょうか？

回答No.3

osamuy

No.2、に追加。sheetcopyの修正が抜けてました。

Sub sheetcopy（）

を

Sub sheetcopy（rnum As Long）

――にしといて下さい。

お礼

どうも、ありがとうございました。
私の実現させたいことが、正確に動作いたしました。
さらに、今後のアドバイスも頂きうれしゅうございました。

回答No.2

osamuy

サブルーチン「セル番地」を関数として定義し、サブルーチン「sheetcopy」に引数ｒｎｕｍを受け取るよう修正しては。

Function セル番地（）
Worksheets("Sheet1").Select
Cells(2, 2).Select
Range(Selection, Selection.End(xlDown)).Select
rnum = Selection.Rows.Count - 1
MsgBox rnum
セル番地 = rnum
End Fucntion
sub sheetcomplete (sht_name as string)
Dim rnum as Long
rnum = セル番地()
Worksheets(sht_name).Select
Call sheetcopy( rnum )
End sub
Public Sub main()
sheetcomplete "Sheet2"
sheetcomplete "Shhet3"
End Sub

プログラミングをやりだしたら、単にExcelの本だけなく、情報科学一般の本（プログラミング概論とかアルゴリズムの本とか）も併読する事を、おすすめします。
新たな視点が得られて、問題解決に役立つ事が多いので。

補足

■「セル番地」を関数として定義し、は以下に該当するところでしょうか？

Function セル番地（）

セル番地 = rnum ←ここで記載されているものは、どのような意味なのでしょうか？

end function

■サブルーチン「sheetcopy」に引数ｒｎｕｍを受け取るよう修正しては。

Call sheetcopy( rnum ) ←これが上記のご説明部分に該当するところですか？

**************************************
関数として定義するとちゃんと動くのですが、SUB セル番地（）とすると、”関数、変数を定義してください”なる表示がでました。

（自分で文献を読む前にご質問して恐縮ですが、）”関数として定義する” 意味、利用の方法など、よろしかったらご教授ください。

回答No.1

gif317

＞変数rnumを、”sheetcopy”に記載されている変数rnumへ渡すためにはどのようにしたらよいでしょうか？

つまりプロシージャを超えた変数を利用したいのですね？それなら、

Option Explicit
Dim rnum As Integer 'これでモジュール中で共通に使える

と一番上に書けばこの変数rnumはどのプロシージャ間でも使えます。困難でいいでしょうか？

お礼

ありがとうございました。まさに、質問のポイントに対する回答でした。