excel VBA コード編集のアドバイス

質問

下記データベースをexcelで作成しようとしています。

■「data」シート
・・・データを入力するシート

■「input]シート
・・・データを入力してdataシートに格納する、またdataシートで入力した値をほしい情報を表示させるシート

■やろうとしていること、困っていること
・・・dataシートのＡ列でＩＤ番号を入力しておりますが、dataシートでオートフィルタを使う場合があり、その場合、ＩＤが飛び飛びになるのですが、下記コマンドボタンだと、それに合わせて飛んでくれません。

■相談したいこと
これを解決するために、オフセットでは、無理ということは分かるのですが、じゃぁどういったコードを組めばいいのかをアドバイスをお願いできないでしょうか。どうかよろしくお願いいたします。

'■以下コードです。
Public trg As Range
Sub Mae()
'前へのボタン
If trg.row >= 3 Then
Set trg = trg.Offset(-1, 0)
Call Tenki
Else
MsgBox "これより前のレコードはありません"
End If
End Sub

Sub Tsugi()
'次へのボタン
If trg.row < Worksheets("data").Range("A60000").End(xlUp).row Then
Set trg = trg.Offset(1, 0)
Call Tenki
Else
MsgBox "これより後ろのレコードはありません"
End If
End Sub

Sub Tenki()
Worksheets("input").Range("c4").Value = trg.Offset(0, 0)

End Sub

回答No.6

nayuta_lot · ベストアンサー

こんにちは

前へのコードですが、段落つけてみましょう

'前へ移動する

Sub Mae()

　　If trg.Row > Worksheets("data").Range("a1") Then

　　　　Do
　　　　　　　Set trg = trg.Offset(-1, 0)

　　　　　　　ElseIf trg.Row = Worksheets("data").Range("a1") Then

　　　　　　　　　　　MsgBox "これより前のレコードはありません"

　　　　　　　　　　　Exit Sub

　　　　　　　Else

　　　　Loop Until trg.EntireRow.Hidden = False

　　　　Call Tenki

　　End If

End Sub

外側からくくってみると

Sub Mae()

　　If ～

　　　　Do
　　　　　　～
　　　　　　ElseIf

　　　　　　Else

　　　　Loop

　　End If

End Sub

となります。　Do とLoopの間にElseIf、Elseが存在すると見なされるため、IFがないと
「コンパイルエラー　elseに対応するifがありません」となります。

基本の内容から、考え直してみましょう。

Sub Mae()

　　If trg.Row >= 3 Then

　　　　Do
　　　　　　Set trg = trg.Offset(-1, 0)

　　　　Loop Until trg.EntireRow.Hidden = False

　　Else

　　　　MsgBox "これより前のレコードはありません"

　　End If

End Sub

基本的にこれで、前の可視セルに移動できますが、Ａ１も可視セルであるため
タイトルも対象となります。

そこで、Loopから抜けた後に、trg.Rowがタイトル行を示していないか調べます。
調べるためには、trgの行、つまりIf trg.Row = 1　で調べられますね。

１であれば、"これより前のレコードはありません"のメッセージを表示させます。
※すでに、上記のメッセージがでない条件に入っているためです。

行き過ぎたtrgの数値を下限（つまり最初）に戻すためには、「最初」の処理をして
あげればよいですね。
そのあとは、転記する必要がないので、Exit Subで抜けます。

逆に、タイトル行まで行っていなければ、前の行をあらわしているので転記します。

これをコードに書くと次のようになります。

　　　　If trg.Row = 1 Then

　　　　　　　MsgBox "これより前のレコードはありません"

　　　　　　　Call Saisyo

　　　　　　　Exit Sub

　　　　Else

　　　　　　　Call Tenki

　　　　End If

このコードをLoopが終わった後に入れますので、最終的には以下のようになります。

Sub Mae()

If trg.Row >= 3 Then

Do

Set trg = trg.Offset(-1, 0)

Loop Until trg.EntireRow.Hidden = False

If trg.Row = 1 Then

MsgBox "これより前のレコードはありません"

Call Saisyo

Exit Sub

Else

Call Tenki

End If

Else

MsgBox "これより前のレコードはありません!"

End If

End Sub

上記のコードは、この質問コーナーで回答を登録すると段落が付きませんが（笑）
一度、段落を付けて確認してみてください。

If　～　End Ifの中はタブで一段下げる、Do ～　Loopの中はタブで一段下げる、
というようにブロックごとに段落をつけると構文エラーも発見しやすくなります。

プログラムの視認性を向上させることも上達の秘訣のひとつです。

なにかあれば補足してください。＾＾
それでは

お礼

nayuta_lot様

大変勉強になりました。

ご説明いただいた内容を理解するのに時間がかかりましたが、とても分かりやすかったです。

このようなプログラムをサラッと理解して、修正していただける上級者の方々には頭が下がる思いと同時に感謝が尽きません。

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

回答No.5

nayuta_lot

こんにちは

言い忘れましたが、【前】の処理で行き過ぎたtrgの値を下限に戻すために、
Exit subの前に【最初】の処理を実行することをお忘れなく・・・
それでは

回答No.4

nayuta_lot

こんにちは

ヒントです。

mitarashiさんのアドバイスで次と最後はできるようになったようなので、その延長で説明します。

【最初】について

最初のデータは、Ａ１つまりタイトル行から見れば、フィルターのあり、なしに関係なく、
次の可視セルですよね。

これって、どこかでみたことありますね。

つまり、Ａ１を現在位置にして、『次の可視セル』を求めればいいわけです。
うーん・・ヒントというより、答えいっちゃってますが（笑）、コードは考えてみてください。

【前】について

行き過ぎてタイトル（Ａ１）までいってしまうということは、もうTenkiの処理をしなくて
いいわけです。

ということは、trg.rowがＡ１に達していたら、そこでExit subしちゃえばいいのです。
あと、この処理をする段階では、フィルターがかかっていて最初の

　"これより前のレコードはありません"

の処理に引っかからないので、Exit subする前に、もう一回

　"これより前のレコードはありません"

という表示の処理を入れてあげればいいでしょう。　"もう前のレコードがありません"
とか言葉かえてみると、フィルターがかけたときの違いがわかりますよ。

んーヒントなのか答えなのかわかりませんが（笑）、健闘を祈ります。

なにかあれば補足してください。
それでは

お礼

補足ありがとうございます。

また、いつもたびたびすみません。

nayuta_lot様から
頂いたヒントを元に、参考書を見ながら下記コードを作成いたしました。
最初への移動はうまく行ったのですが、
前への移動が（ちゃんと理解出来ているのか怪しいのですが・・・）、「コンパイルエラー　elseに対応するifがありません」と言われてエラーになってしまいます。

また、下記コードの確認をお願いしてよろしいでしょうか？

すぐに確認していただきやすいように、下記にファイルをＵＰさせていただきました。(NO4508)
※ウィルス対策ソフトで監視しているＰＣで作成したファイルですので、危険性等はないはずです。
http://www.kent-web.com/pubc/book/test/uploader/uploader.cgi

以下コードの抜粋です
'最初へ移動する
Sub Saisyo()
Set trg = Worksheets("data").Range("a1")

Do
Set trg = trg.Offset(1, 0)
Loop Until trg.EntireRow.Hidden = False
Call Tenki

End Sub

'前へ移動する
Sub Mae()

If trg.row > Worksheets("data").Range("a1") Then

Do
Set trg = trg.Offset(-1, 0)

ElseIf trg.row = Worksheets("data").Range("a1") Then
MsgBox "これより前のレコードはありません"
Exit Sub

Else
Loop Until trg.EntireRow.Hidden = False
Call Tenki

End If

End Sub

回答No.3

imogasi

自分流のコードだけ書いて、やりたいことが、質問ではっきりしない。
オートフィルタでフィルタ後でも見えている（可視）行だけ処理したいということか？
ーー
文章が判りにくい。
＞・データを入力してdataシートに格納する、またdataシートで入力した値をほしい情報を表示させるシート
＞下記コマンドボタンだと、それに合わせて飛んでくれません。
非表示の行は処理をしないということか。
ーー
データ例をあげて、それに沿って、どうしたいのか、（結果はどうなってほしいのか）文章で説明してくれたほうが判りやすい。
経験して、パターン（型・タイプ）というものをとらえ、智識を整理し、表現する勉強が必要だと思う。

お礼

理解不足・経験不足で申し訳ございません。
以後、分かりやすい表現を心がけて投稿するように努めます。

回答No.2

mitarashi

速度は保証の限りではありませんが、例えば「次へ」のケースなら、簡単には、

Do
Set trg = trg.Offset(1, 0)
Loop Until trg.EntireRow.Hidden = False

でいかがでしょうか。ご参考まで。

お礼

mitarashi様

ありがとうございます。
早速ご提示いただいたコードを試したところ、とてもうまくいきました。大変ありがたいです、ありがとうございます。これで、『次へ』と『最後へ』の部分に関しては、オートフィルタの使用に対応できました。

これを応用して、『前へ』と『最初へ』のコードも修正しようとしたんですが、フィルタで非表示にしているセルは飛ばしてくれるんですが、最終的に『前へ』行き過ぎてタイトル行まで選択してしまいます。

教えてばっかりで大変恐縮なのですが、、『前へ』と『最初へ』のコードのヒントも頂戴できないでしょうか？

回答No.1

nayuta_lot

こんにちは

前回の質問の続きだと思いますが、最初、最後の部分、どうしちゃいました？
このままだと、いきなり、

　『オブジェクト変数またはWithブロック変数が設定されていません』

のエラーがでちゃいますが・・
この質問だけみた人は判らないと思いますので補足をしたほうがよいです。

あと、Tenki()のところも

　Worksheets("input").Range("c4").Value = trg.Offset(0, 0)

と"input"がでてきてますが、"User Sheet"の名前が変わっただけですかね＾＾；

この質問だけみた人には関係ないのですが、補足をお願いします。

補足

nayuta_lot様
いつもご指導・ご指摘ありがとうございます。

ご指摘の通り、データベース作成の一連作業を進めていく上で、つまづいてしまう所が多々あり、その都度お世話になっております。sheet名等は作業の都合上変更させていただきました。

ここの質問では、私の理解不足で、必要なコードを省略してしまいました。
ここに改めて、必要なコードを盛り込んで記載させていただきます。

■やりたいこと
オフセットで前後のレコードを選択するのではなく、フィルタで表示されていないレコードを飛ばして前後のレコードが選べるようなコードをつくりたいと思います。

'■以下標準モジュールに記載したモジュールです。
Public trg As Range
Sub Saisyo()
Set trg = Worksheets("data").Range("A2")
Call Tenki
End Sub

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

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

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

Sub Tenki()
Worksheets("input").Range("c4").Value = trg.Offset(0, 0)

End Sub

'■This Workbook に記載したモジュールです。
Private Sub Worksheet_Activate()
Call Saisyo
End Sub