エクセルＶＢＡ 検索データの表示方法

質問

大変困っているので、どなたか教えてください。顧客管理ツールを作っています。

顧客データを保有するエクセルブックＡがあり、１顧客につき５１個のデータを持っています。オートフィルタのあいまい検索によりヒットしたデータを別のエクセルブックＢの（シート名：ＨＩＴＤＡＴＡ）へコピーします。

（シート名：ＨＩＴＤＡＴＡ）に抽出された各顧客データを同じブックＢの（シート名:ＵＳＥＲ）に１顧客ごとの情報がわかるようにデータを表示させます。そして「次へ」「前へ」「最初へ」「最後へ」のコマンドボタンを押すことで、（シート名：ＨＩＴＤＡＴＡ）に抽出されたそれぞれのデータ参照からの表示をコマンドボタンの意味通りに変更させたいのですが、どうすればいいのでしょうか？

「最初へ」と「最後へ」は、End(elUP)とEnd(elDown)を使って何とかできるのですが、「次へ」と「前へ」が出来ません。どのようなコードを書けばよろしいか、どなたか教えてください。

回答No.2

zap35 · ベストアンサー

＃０１です。例えばですが、標準モジュールに以下を貼り付けます。
Sisyo(),Saigo(),Mae(),Tsugi()の各サブモジュールをボタンに割り付けるとテストしやすいと思います

Public trg As Range
Sub Saisyo()
　Set trg = Worksheets("Hit Data").Range("A3")
　Call Tenki
End Sub

Sub Saigo()
　Set trg = Worksheets("Hit Data").Range("A60000").End(xlUp)
　Call Tenki
End Sub

Sub Mae()
　If trg.Row >= 4 Then
　　Set trg = trg.Offset(-1, 0)
　　Call Tenki
　Else
　　MsgBox "これより前のレコードはありません"
　End If
End Sub

Sub Tsugi()
　If trg.Row < Worksheets("Hit Data").Range("A60000").End(xlUp).Row Then
　　Set trg = trg.Offset(1, 0)
　　Call Tenki
　Else
　　MsgBox "これより後ろのレコードはありません"
　End If
End Sub

Sub Tenki()
　Worksheets("User Sheet").Range("D9").Value = trg.Offset(0, 0)
　Worksheets("User Sheet").Range("D10").Value = trg.Offset(0, 1)
　Worksheets("User Sheet").Range("D11").Value = trg.Offset(0, 2)
　Worksheets("User Sheet").Range("D12").Value = trg.Offset(0, 3)
End Sub

ただし変数trgはSaisyo()、Saigo()が起動される前は不定なので、"User Sheet"のシートモジュールに以下を貼り付けます（シートモジュールはシート名右クリック→コードの表示で開く画面）

Private Sub Worksheet_Activate()
Call Saisyo
End Sub

お礼

zap35さんへ

すごい！！！出来ました。
標準モジュールに記入することは全く思いつきませんでした。操作の繰り返しだと思い、ずっと各コマンドボタンのCLICKに「Loop」や「Next」を記入して悩んでいました。
ありがとうございました。

回答No.1

zap35

良く理解していないまま回答しますが、HITDATAシートのある行をUSERシートに表示するのにどのようなVBAを使っていますか？

Worksheets("USER").Range("A1").Value = Worksheets("HITDATA").Cells(i, y).Value
のようにしているなら「次へ」で「i」の値に１加算すればHITDATAの１行下を参照できます

そうではなく表示する対象をRange Objectで持っているなら
set trg = trg.Offset(1,)
で１行下に移動することが可能です

補足

zap35さん。早々の回答ありがとうございます。

HITDATAシートのある行をUSERシートに表示する場合には、まず、抽出したデータの最初のコードをアクティブに設定し、以下のコードを書いています。

Worksheets("User Sheet").Range("D9").Value = ActiveCell.Offset(0, 0)
Worksheets("User Sheet").Range("D10").Value = ActiveCell.Offset(0, 1)
Worksheets("User Sheet").Range("D11").Value = ActiveCell.Offset(0, 2)
Worksheets("User Sheet").Range("D12").Value = ActiveCell.Offset(0, 3)
続く・・・。

と言うように、全てのセルにアクティブセルを参照させてoffsetにて貼り付けています。

ちなみに「最初へ」には、
Dim srow As String
Worksheets("User Sheet").Range("D9").Value = Worksheets("Hit Data").Range("A3")
Worksheets("User Sheet").Range("D10").Value = Worksheets("Hit Data").Range("B3")
Worksheets("User Sheet").Range("D11").Value = Worksheets("Hit Data").Range("C3")
続く
と、具体的なセルを指定するコードを、

「最後へ」は、
Range("D9") = Worksheets("Hit Data").Range("A60000").End(xlUp)
Range("D10") = Worksheets("Hit Data").Range("B60000").End(xlUp)
Range("D11") = Worksheets("Hit Data").Range("C60000").End(xlUp)
続く
と記載しています。

ＶＢＡ初心者なので、お願いできるならば、記載必要なコードを教えていただけると助かります。よろしくお願いします。