別シートの行を選択し対象列を転記するマクロ

質問

シート２のＡ～Ｆ列までデータがあります。
行数は10,000行です。（月ごとに100行くらい追加されます。）
Ａ列は項目がＮＯ．で半角数字が連番で入力されてます。

セルA2→1
セルA3→2
セルA4→3
．
．
．
セルA10000→9999
セルA10001→10000
という感じです。

シート１の
セルI9に番号を入力すると
シート２のその番号の行のＢ～Ｆ列の値を
シート１のJ9～N9列に転記したいです。

例えば
シート１のセルI9に100と入力したら、
シート２のＡ列が100と入力されているセルはA101ですから
101行目となります。101行目の各列の値を転記します。
↓
シート１のセルJ9にシート２のセルB101の値を転記
シート１のセルK9にシート２のセルC101の値を転記
シート１のセルL9にシート２のセルD101の値を転記
シート１のセルM9にシート２のセルE101の値を転記
シート１のセルN9にシート２のセルF101の値を転記

シート１もシート２もセルの書式設定は標準。
Ｂ，Ｅ，Ｆ列は半角英数字の組み合わせで
Ｃ，Ｄ列は半角数字のみです。
たまにＦ列に空白がある行があります。
Ｂ，Ｆ列は数字のみの場合もあります。
Ｂ，Ｃ，Ｄ，Ｆ列は数字のみの場合
「数値が文字列として入力されています」
となっています。

マクロで行いたいのですが記述そのものを教えてください。

回答No.1

merlionXX · ベストアンサー

gx9wx ちゃん、昨日今日と質問のラッシュですね、どうしたの？

で、一例です。
おわかりとは思いますがSheet1のシートモジュールに書いてください。
シート１、シート２とお書きですが、Sheet1,Sheet2ですよね？

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Address(0, 0) <> "I9" Then Exit Sub
　　On Error GoTo line
　　Application.EnableEvents = False
　　Target.Offset(, 1).Resize(, 5).Value = Sheets("Sheet2").Range("B1").Offset(Target.Value).Resize(, 5).Value
　　MsgBox Sheets("Sheet2").Range("B1").Offset(Target.Value)
line:
　　Application.EnableEvents = True
End Sub

お礼

すいません。
一度に何個も頼まれて．．．．．．
さばけないです。
そもそも私に依頼する方がまちがっているのに．．．．．．
　できない（時間的にではなくその能力がそもそもないので）
　と言っているのですが聞いてもらえません）
前回の質問と連結させるつもりでしたが
前回のが
Private Sub Worksheet_Change(ByVal Target As Range)で
行うとなると、もう構想がずれてお手上げです。

Ｉ９に数字を入力したら
↓
このスレッドで教えていただいたマクロが走って
5ケ所の値が変わったら
↓
最初からある別マクロが走って、
↓
I10に数値を入力したら頭Qの9桁に変換してD15に転記
（別スレで回答いただいた、
　数字を入力したら頭Qの9桁に変換する物）

もう完全に構想が外れてパニくって混乱して
なんだかわからなくなりました。
マクロが走って５ケ所の値が変わったら
またマクロが走って、というのは出来るのでしょうか？
お手数かけます。申し訳ありません。

回答No.4

merlionXX

> シートの保護をした状態にて例えばセルA1に=K1とか式が入っている場合は
> セルA1に「ロック」がチェックされていもK1の値が変わればA1の値も変わります。

はい、数式の計算結果が変わっただけで入力されたわけじゃないですから当然ですね。
チェンジイベントも働かないでしょ。

> ですが今回のようにマクロで転記する場合はその転記先のセルが「ロック」がチェックされていると正常衝動作しません。
> これは当たり前なんでしょうか？

マクロは手入力の代わりをしてくれるわけですからあたりまえです。
もっとも、ロックしたセルに入力を可能にするマクロの書き方もありますけどね。
で、この質問と
http://okwave.jp/qa/q6463730.html
この質問、ごっちゃになっちゃいましたね。
結局、シート保護が原因だったってわけ？
大山鳴動鼠一匹ってわけでしたか。

あと、古い
http://okwave.jp/qa/q6404265.html
も、もう締めましょうよ。

> 一度に何個も頼まれて．．．．．．
> さばけないです。

gx9wxちゃん、もう会社じゃマクロの達人と思われてるんでしょうね。
では、わたしは今夜もこれから新年会のお座敷に参ります。
いったい今年何回目の新年会なんでしょね。（笑）

お礼

すいません。
これは帳票を作成します。
使用ユーザーのスキルに合わせ
誤操作による雛形の破壊を防ぐために
シート保護をかけています。
申し訳ありませんでした。

マクロ．．．．．

全然できないのに．．．．．．．

困っています。

回答No.3

tom04

こんにちは！
横からお邪魔します。

関数の方が簡単だと思いますが・・・

Sheet1のJ9セルに
=IF($I9="","",INDEX(Sheet2!$B:$F,$I9+1,COLUMN(A1)))
という数式を入れ、列方向にオートフィルでコピーではダメですか？

どうしてもVBAがお望みならChangeイベントではなく、別マクロにして、その都度マクロを実行するのはどうでしょうか？
一例です。

Sub test()
Dim i, j As Long
Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
i = ws1.Range("I9").Value
If ws1.Range("I9") = "" Or i > ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1 Then Exit Sub
For j = 2 To 6
ws1.Cells(9, j + 8) = ws2.Cells(i + 1, j)
Next j
End Sub

こんな感じではどうでしょうか？

的外れならごめんなさいね。m(__)m

お礼

>Sheet1のJ9セルに
>=IF($I9="","",INDEX(Sheet2!$B:$F,$I9+1,COLUMN(A1)))
>という数式を入れ、列方向にオートフィルでコピーではダメですか？

最初はそうやっていました。
ただマクロも組み込んであるので
ついでにマクロに入れようかなと思いました。

＞都度マクロを実行するのはどうでしょうか？
＞一例です。

これ使えます。
これは帳票を作成します。
よって作業者が数字を入力後最終的には
印刷をします。
印刷ボタンを画面につけたので

１．数値を入力
２．画面に作成した「印刷」ボタンをクリック
３．最初からあるマクロが走って
４．教えていただいたSub test()が走って
５．印刷処理のマクロが走って
６．プリンタで印刷

というふうにできます。
別な帳票に流用できそうです。

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

回答No.2

merlionXX

勘違いしてたらごめんなさい。
I9に入力があったらJ9:F9の値が自動でかわるんでしょ？
ならばわざわざJ9にまでトリガーを設定せず、I9に入力して、J9:F9への自動入力が完了した段階で、Sub　ハイフン挿入0M　をCallすればいいだけじゃないんですか？

> I10に数値を入力したら頭Qの9桁に変換してD15に転記

これは分岐できますよね。もう回答済みだけど。

お礼

ありがとうございます。
勘違いではないです。大丈夫です。
向こうの方の記述で行いましたが
なぜか動きません。
すいません。m(__)m

補足

ちょっとズレてしまいますが、
シートの保護をかける時
特定のセルを
「セルの書式設定」→「保護」
で「ロック」のチェックをはずすと
そのセルは保護がかかりません。

シートの保護をした状態にて
例えば
セルA1に
=K1
とか式が入っている場合は
セルA1に「ロック」がチェックされていも
K1の値が変わればA1の値も変わります。

ですが今回のようにマクロで転記する場合は
その転記先のセルが「ロック」がチェックされていると
正常衝動作しません。

これは当たり前なんでしょうか？
実際にそうみたいなので当たり前なんですよね？m(__)m