エクセルのマクロで、セルの値を参照してジャンプ

質問

エクセルのマクロで、セルの値を参照して、
別シートにジャンプして貼り付けたいと思っています。

<Sheet1>
　|　　A　　B　　C
------------------------------------
1| 　A1　　あ　　100
2| 　B5　　い　　50
3| 　C7　　う　　80
4| 　D3　　え　　20
5| 　E9　　お　　40

<Sheet2>
　|　　A　　B　　C　　D　　E
------------------------------------
1|　　あ
2|　　100
3|　　　　　　　　　え
4|　　　　　　　　　20
5|　　　　い
6|　　　　50
7|　　　　　　　う
8|　　　　　　　80
9|　　　　　　　　　　　　お
10|　　　　　　　　　　　　40

といったイメージです。（お分かり頂けますか？）

Sheets1!B1:C1を選択し、A1のセルの値を参照して、Sheets2!A1にジャンプして、
行列入れ替えて貼り付ける、といったマクロを組みたいのですが、
セルの値を参照する部分が分かりません。
実際はSheets2の様なシートは複数あり、1つのリストから複数の形式を変えた表を
作りたいと思っています。

Sheets2以降のシートに見出し行を加え、VLOOK関数を組み込んだ表を作っておき、
結果を貼り付けて見出し行を削除する、といったマクロは組めるのですが、
セルを参照してジャンプして貼り付けてくれると便利だと思い質問させて頂きました。

どなたかお知恵を拝借できましたら嬉しいです。
宜しくお願い致しますm(_ _)m

回答No.6

Wendy02 · ベストアンサー

こんばんは。

>棚番号を認識しないのか、表の数値全てをSheet2（表1）にコピーされてしまいました。

棚番号がどこに入るのか分かりませんでしたね。
棚というのは、シートのことですか？（それは気が付きませんでしたね（^^;）
うっかりしていましたね。

だから、以下のように書きました。
Tana = .Cells(i, 2).Value '棚　'未使用

------------------------------------------------

Sub TestMacro1R()
　　Dim Sh1 As Worksheet
　　Dim Sh2 As Worksheet
　　Dim Sh3 As Worksheet
　　Dim Sh4 As Worksheet
　　Dim Shes As Variant　'シートを配列に入れる
　　Dim i As Long
　　Dim j As Long
　　Dim Ren As Integer
　　Dim Komo As String
　　Dim Tana As Integer
　　Dim Kin As Long
　　Dim Retu As Integer
　　Dim Gyo As Integer

　　Dim LastRow As Long
　　Set Sh1 = Worksheets("Sheet1")
　　Set Sh2 = Worksheets("Sheet2") '1
　　Set Sh3 = Worksheets("Sheet3") '2
　　Set Sh4 = Worksheets("Sheet4") '3
　　Shes = Array(Sh2, Sh3, Sh4)

　　'最後の行
　　LastRow = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

　　With Sh1
　　　　For i = 2 To LastRow
　　　　　　Ren = .Cells(i, 1).Value '連番
　　　　　　Komo = .Cells(i, 5).Value '項目
　　　　　　Tana = .Cells(i, 2).Value '棚　'未使用
　　　　　　Kin = .Cells(i, 6).Value '金額
　　　　　　j = .Cells(i, 4).Value '行
　　　　　　Gyo = (j - 1) * 3 + 1
　　　　　　Retu = .Cells(i, 3).Value '列

　　　　　　With Shes(Tana - 1).Range("B1") '基点(連番項目の一番上）

　　　　　　　　.Offset(Gyo, Retu).Value = Ren
　　　　　　　　.Offset(Gyo + 1, Retu).Value = Komo
　　　　　　　　.Offset(Gyo + 2, Retu).Value = Kin
　　　　　　End With
　　　　Next i
　　End With
　　Set Sh1 = Nothing
　　Set Sh2 = Nothing
　　Set Sh3 = Nothing
　　Set Sh4 = Nothing
　　Erase Shes
End Sub

ところで、私は、今は、Excelとかは関係のない生活をしているのですが、仕事の頃は、オンザジョブで、プログラム・メインテは、2年掛かりで直していきます。作るのは、1週間程度ですが、毎月少しずつ構築していくわけです。特に、年度の境目と年の境目でバグが出る可能性があります。仕事のときは、こういう集計は誰もいなかったので、忙しいときはプログラム実行に頼らなくてはならないのです。しかし、バグ続きでした。それを会社に提出するわけで、もう、惨憺たる状態からのスタートでしたね。

でも、重ねて書きますが、その原票の筆記（実務上はかなり難しい）に信頼性があるなら、完璧な在庫管理表だと思います。普通は、一覧表から、商品名、数量、金額を割り振りして分別していくというという方式が多いです。

お礼

素晴らしいです！正に望んでいた完璧な結果です。
これを応用できるよう、自分で理解を深めていきます。
本当にありがとうございました。

＞もう、惨憺たる状態からのスタートでしたね。
きっと膨大な量でしょうから、ご苦労が伺えますね。
でもそういう経験がおありだからこそのスキルなのでしょう。
私もしばらく実務でのExcelは離れ、最近また必要になって
きましたが、奥が深くて面白いものですね^^

さて、ポイントを付けさせて頂かないといけないのですが、
Wendy02さんにはその後色々とレクチャーして頂いたので
良ポイントとして、次点の方に迷ってしまいます。
今回はご回答頂いた順で付けさせて頂きたいと思います。
皆さま、ありがとうございましたm(_ _)m

回答No.5

Wendy02

こんにちは。

>今回はワークシート関数を組み込んだ方が速いような気はしますが、今後のために勉強してみたいです。

ワークシートも、大きさにもよりますね。私は、マクロ専門だから、マクロで処理するというわけですが、関数が良く分かっている人は、マクロでのアイデアも同じようにできるものだと思います。

サンプルを見せていただいたけれども、合理的に数学的に作っていますね。ちょっとしたヒントさえあれば、ご自身でも可能だと思います。今回は、勉強のためのコードとして、なるべく基本に忠実に考えてみます。

私が想像していたのは、もっとややこしくなっているものだと思いました。多くの質問は、実務として、ワークシート関数では、不可能だから、マクロでお願いしますというのが多いです。

なるべく易しいコードを考えてみました。（途中で、ローカルウィンドウで値も取れます）
----------------------------------------------------

Sub TestMacro1()
　　Dim Sh1 As Worksheet
　　Dim Sh2 As Worksheet
　　Dim i As Long
　　Dim j As Long
　　Dim Ren As Integer
　　Dim Komo As String
　　Dim Tana As Integer
　　Dim Kin As Long
　　Dim Retu As Integer
　　Dim Gyo As Integer

　　Dim LastRow As Long
　　Set Sh1 = Worksheets("Sheet1")
　　Set Sh2 = Worksheets("Sheet2")

　　'最後の行
　　LastRow = Sh1.Cells(Rows.Count, 1).End(xlUp).Row

　　With Sh1
　　　　For i = 2 To LastRow
　　　　　　Ren = .Cells(i, 1).Value '連番
　　　　　　Komo = .Cells(i, 5).Value '項目
　　　　　　Tana = .Cells(i, 2).Value '棚　'未使用
　　　　　　Kin = .Cells(i, 6).Value '金額
　　　　　　j = .Cells(i, 4).Value '行
　　　　　　Gyo = (j - 1) * 3 + 1
　　　　　　Retu = .Cells(i, 3).Value '列

　　　　　　With Sh2.Range("B1") '基点(連番項目の一番上）
　　　　　　　　.Offset(Gyo, Retu).Value = Ren
　　　　　　　　.Offset(Gyo + 1, Retu).Value = Komo
　　　　　　　　.Offset(Gyo + 2, Retu).Value = Kin
　　　　　　End With
　　　　Next i
　　End With
　　Set Sh1 = Nothing
　　Set Sh2 = Nothing
End Sub

----------------------------------------------------
>棚＆列＆行で「132＝Sheet2!E5」といった表を作り、
>参照する方法はできないかな。。。とも思ったのですが
>現実的ではないでしょうか？

この方法でも、できますが、「棚,列,行,位置」というように、コンマ切りのほうが、処理しやすいです。それより、そういう入力方法が大変ではないでしょうか？実務的には、テンキーだけで入力できれば、一番よいですよね。区切り文字で、「,」はありませんが、「/*-+.」は使えますから、それで切り分けて作れば早いと思います。「132＝Sheet2!E5」　->「1.3.2.2-5.5」
こういう約束事は、慣れれば覚えられます。

それから、このマクロ自体よりも、元の原票を作るほうが大変なような気がします。マクロで並べ替えてみて気が付いたけれども、この原票が出来上がった時点で、8割以上出来上がっているのも同じだと思います。この原票を手作業で作っているとしたら、非常に合理的に棚の整理ができているのだと思います。場所が、ぶつかることはないのでしょうか？

お礼

ご教授ありがとうございます。
頂いたマクロの結果では、棚番号を認識しないのか、
表の数値全てをSheet2（表1）にコピーされてしまいました。
少しだけいじってみて
　With Sh2.Range("B1") '基点(連番項目の一番上）
以降の式や他の部分もSh3を加えたのですが、　
今度はSheet3にも全てコピーされてしまい、
棚とシートを認識する部分がわかりませんでした。

おっしゃる通り、ワークシート関数＋
結果のコピーや不要行の削除という初歩的なマクロで
対応できる範囲ですね^_^；
マクロは記録から少しだけ応用するという程度しかできないのですが、
より効率的な作業ができないかなと思い質問させて頂きました。
マクロをもう少し扱えた上で、ワークシート関数が適しているのか、
マクロが適しているのか判断できる知識が必要だと思いました。

上の意向としてはリストから棚の表を作るイメージですが、
確かにぶつかってしまう可能性があります。
逆に棚からリストを作る方が効率的かもしれませんね。

もう少し勉強してから、また改めて別途質問させて頂くかもしれません。
その際はどうぞよろしくお願いいたします。
ありがとうございましたm(_ _)m

回答No.4

Wendy02

こんにちは。

>マクロ初心者で配列変数も使った事がないのですが、そちらの方が早いのでしょうか？

配列変数自体は、覚えるまでに少し時間が掛かります。掲示板で配列を書いている人でも、必ずしも、理解しているとは限りません。

ここでいう配列というのは、

　　範囲.Value = 範囲.Value

セルひとつではなく、範囲.Value という取り方をすると、配列という形で、格納させるということです。配列には、数値か文字等の情報しか入っていません。その他の書式情報や数式情報などは含まれていませんので、スピードが速いということになります。

また、見易さの点でも上です。しかし、実際の中身を理解して扱えるまでには、「配列」の二次配列まで学ばないといけません。非常に特殊な表計算独特の「配列」です。

>簡単で応用しやすい方法があれば、お教え頂けましたら幸いです。
>列番号＆行番号はセル位置の番号とは異なります。

実務としては、ワークシート関数を再利用するという方法が取られます。

i は、Variant 型に限ります。

i=　Application.Match(検索値,一列または一行の範囲,0)

If Not IsError(i) Then
　　i番目
End if

という裏技で処理します。Application.Match という書き方は、Excel 2000 以降では、古い書き方ですが、マクロを専門的に書かない人には便利な方法だと思います。

また、表の何行目の何列目という場合は、Range("B1:D10") としたら、

Range("B1:D10").Cells(1,2)

B1:D10　の、1行目の2列目　ですから、これは、C1 ということになります。

具体的には、サンプルをお出しになっていただけたら、もう少し説明しやすいかと思います。

お礼

お早いご回答ありがとうございました。
配列変数というのは、なかなか難しそうですね。。。
お教え頂いたものも、すぐにはイメージができません^_^；

具体例に近いのは以下の様な形式です。

<Sheet1>
　|　　A　　　B　　　C　　　D　　　E　　　F
-----------------------------------------------
1| 　連番　　棚　　　列　　行　　項目　　金額　
2| 　　1　　　1　　　3　　　2　　　あ　　100
3| 　　2　　　3　　　1　　　3　　　い　　50
4| 　　3　　　2　　　3　　　1　　　う　　80
5| 　　4　　　1　　　2　　　3　　　え　　20
6| 　　5　　　2　　　1　　　2　　　お　　40

<Sheet2>
　|　　A　　　B　　　C　　　D　　　E
--------------------------------------------
1|　　　　　　　　　１　　　２　　　３
2|　　１　　連番　　　
3|　　　　　項目　　　　　　　　
4|　　　　　金額　　　　　　　　
5|　　２　　連番　　　　　　　　　　1　　　
6|　　　　　項目　　　　　　　　　　あ　　　　
7|　　　　　金額　　　　　　　　　　100　　　　　　　
8|　　３　　連番　　　　　　4　　　　　　
9|　　　　　項目　　　　　　え　　　　　　　　　　　　　　　　
10|　　　　　金額　　　　　　20　　　　　　　　　　　　　　

<Sheet3>
<Sheet4>

棚＆列＆行で「132＝Sheet2!E5」といった表を作り、
参照する方法はできないかな。。。とも思ったのですが
現実的ではないでしょうか？

現在はリストの件数が100件、表は4つほどになりそうなので
今回はワークシート関数を組み込んだ方が速いような気はしますが、
今後のために勉強してみたいです。
お時間がある時で結構ですので、ご教示頂けましたら幸いです。
宜しくお願いいたします。

回答No.3

Wendy02

こんばんは。

>Sheets1!B1:C1を選択し、A1のセルの値を参照して、
本当は、配列変数で行ったほうが早いとは思いますが、オーソドックスなコピーモードのマクロにしてみました。

Sub Test1()
　　Dim rng As Range
　　Dim cnt As Long
　　Dim i As Long
　　Dim Sh2 As Worksheet

　　Set Sh2 = Worksheets("Sheet2")
　　Application.ScreenUpdating = False
　　With Worksheets("Sheet1")
　　　　Set rng = .Range("A1").CurrentRegion

　　　　For i = 1 To rng.Rows.Count
　　　　　　With Sh2.Range(rng.Cells(i, 1).Value)
　　　　　　　　rng.Cells(i, 2).Resize(, 2).Copy
　　　　　　　　.Cells(1).PasteSpecial , , , True
　　　　　　End With
　　　　Next i
　　End With
　　Application.CutCopyMode = False
　　Application.ScreenUpdating = True
　　Set rng = Nothing
　　Set Sh2 = Nothing
End Sub

お礼

ご回答ありがとうございました。
質問内容通りの結果となりました。
こちらにいらっしゃる皆様は素晴らしいですね！

マクロ初心者で配列変数も使った事がないのですが、
そちらの方が早いのでしょうか？
実際の作業では棚番号＆列番号＆行番号を参照するのですが、
棚番号がそれぞれのシートで、
列番号＆行番号はセル位置の番号とは異なります。
簡単で応用しやすい方法があれば、お教え頂けましたら幸いです。

回答No.2

imogasi

標準モジュールに
Sub test01()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d = sh1.Range("A65536").End(xlUp).Row
For i = 1 To d
sh2.Range(sh1.Cells(i, "A")) = sh1.Cells(i, "B")
sh2.Range(sh1.Cells(i, "A")).Offset(1, 0) = sh1.Cells(i, "C")
Next i
End Sub
ーー
気持ち的にはジャンプですが、ジャンプは関係くできます。

お礼

ご回答ありがとうございました。
きちんと目的通りの結果となりました。
No.1の方とはまた異なり、色々な方法があるのですね。
マクロ初心者なもので、応用しながら勉強してみます。
また質問させて頂く際は、どうぞよろしくお願い致します。

回答No.1

n-jun

Sub　test()
　Dim　r　As　Range
　Dim　rs　As　Range

　With　Worksheets("Sheet1")
　　　　　　Set　rs　=　.Range(.[A1],　.Cells(Rows.Count,　1).End(xlUp))
　End　With

　For　Each　r　In　rs
　　　　　With　Worksheets("Sheet2").Range(r.Value)
　　　　　　　　　　.Value　=　r.Offset(,　1).Value
　　　　　　　　　　.Offset(1).Value　=　r.Offset(,　2).Value
　　　　　End　With
　Next
End　Sub
こうゆう感じとは違うでしょうか？

お礼

ご回答ありがとうございました。
きちんと目的通りの結果となりました。
マクロ初心者なもので、頂いた内容を確認しながら応用してみます。
また質問させて頂く際は、どうぞよろしくお願い致します。