Excel VBAにてソートが上手くいかない

以下のコードを実行しても

With Worksheets("賞味期限管理").Sort
With .SortFields
.Clear
.add Key:=Worksheets("賞味期限管理").Range("D1"), Order:=xlAscending
.add Key:=Worksheets("賞味期限管理").Range("B1"), Order:=xlAscending
.add Key:=Worksheets("賞味期限管理").Range("C1"), Order:=xlAscending
End With
.SetRange Worksheets("賞味期限管理").Range("A5:D" & locate - 1)
.Apply
End With

1回目ではソートが上手くいかず、2回目で正しくソートされます。以降、上手くいかず、正しくソート、を繰り返します。
何が原因かわかりません。
試しに"マクロの記録"でExcel上にてソート設定して実行したものに置き換えて実行してみましたが結果が同じでした。

何卒ご教示願います。

質問者からの補足コメント

• Private Sub worksheet_activate()
  の中で記述しています。
  試しに同じコードを２回書いても結果は同じでした。

    補足日時：2021/04/03 09:24

No.3 回答者： Qchan1962 回答日時： 2021/04/04 08:57

こんにちは

＞B～D列はA列をKeyにVlookupで持ってきた値なので、その辺が影響してるかもしれません。
どんな式なのか分かりませんけど
単純なVLOOKUPなら　Key以外　絶対参照になっていれば、Sortの結果（A列Keyも条件でソートされている）をA列Keyで再計算されるので問題ないのではないでしょう。

Worksheet_Activate内でA列セル値取得マクロは実行されているのでしょうか？
実行されていれば、.SetRange Range("A5:D" & locate - 1)　で良いのかも知れませんが、違うなら
Keyのある列で
locate = Cells(Rows.Count, "A").End(xlUp).Row
With Sort
・
・
.SetRange Worksheets("賞味期限管理").Range("A5:D" & locate)
.Apply
ですね。

＞1回目ではソートが上手くいかず、2回目で正しくソートされます。
これは、謎のままですけれど。。
Worksheet_Activate内で他のシートを選択などしていたりして、、、
内容が解らないので適当になりますが、
対策として想像できるものすべて処理前と後処理を入れてみては？
例えば、Application.EnableEvents

Sort以外に問題がありそうなので、ウォッチやステップ実行などで確認しながら実行すれば解決出来そうな気がしますね。

この回答へのお礼

根気よく回答いただきありがとうございます。
自分もVLOOKUPの参照Keyも一緒にソートされるので問題ないと思っていたのですが、結果を先に言えば、A列をマクロで引っ張ってくるときに、B~D列も一緒に引っ張ってきて、固定値にしたら解決しました。マクロがわかる人間が自分しかいないため、なるべくExcelの関数でやってほしいと頼まれていた事がネックになりました。
また、B～D列をソートKeyにしている場合A列のみソートするということもできなかったため、思い切って関数取っ払ってマクロで直接値を入れました。
1回目はソートされるが2回目がソートされない原因は解らずじまいでしたが、やはりVLOOKUPのソートに原因があったようです。

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

お礼日時：2021/04/04 21:42

No.2 回答者： Qchan1962 回答日時： 2021/04/03 20:59

＞add Key:=Range("D5")~ とすべきですかね？

いいえ、Range("D1")で良いのでは無いかと、、値しだいのところもあるかも、、＃１のコードで上手くいかない、、、？

＞locateには他の表のデータをループして参照し、
＞合致したデータをcaells(locate, 1)に書き込んでいます。書き込んだ後にlocate+1してますので、

このlocate変数 グローバル変数？それともstatic？
範囲を決めるのであれば、D列最終行なのだけれど、、デバッグして値を確かめるべきかと、思います。

End With
debug.print locate '期待する行数（値）ですか？
’又はMsgbox locate
.SetRange Worksheets("賞味期限管理").Range("A5:D" & locate - 1)
.Apply

この回答へのお礼

再度ありがとうございます。
staticですが、試しに”A5:D1000”と固定値でやってみましたが同じでした。（D100までデータが入っていることを確認）
しかしながら少し原因が見えてきました。
A列はマクロで他シートから持ってきた値ですが、B～D列はA列をKeyにVlookupで持ってきた値なので、その辺が影響してるかもしれません。

お礼日時：2021/04/04 01:34

No.1 回答者： Qchan1962 回答日時： 2021/04/03 13:44

こんにちは

原因がどこにあるか、、よくわかりませんが、
locateに原因があるのかも、、デバッグして値を調べてみては？

locateって何を指しているのでしょうか？どのように値を代入しているの？
最終行にSUMなどが入っていて、その手前を意味しているのでしょうか。
-1しているので、
locate = Cells(Rows.Count, "D").End(xlUp).Row　
これかな？

関係ないかもしれませんが、1行目は何が入っているのでしょう。対象は5行目からのようですが、、

Private Sub worksheet_activate()
Worksheets("賞味期限管理")のモジュールに書いているのですかね

参考になるか分かりませんけど、
Worksheets("賞味期限管理")のモジュールとしてまとめると、、
Private Sub Worksheet_Activate()
Dim locate As Long
locate = Cells(Rows.Count, "D").End(xlUp).Row - 1
With Sort
With .SortFields
.Clear
.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending
.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending
.Add Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending
End With
.SetRange Range("A5:D" & locate)
.Apply
End With
End Sub

Sort　Range　Cells　は　ActiveSheetが省略されています。
機能しないと言う事で一応、値でソートしています。
Order:=xlAscendingもデフォルトなので要らない？かな。。

この回答へのお礼

さっそくのご回答ありがとうございます。
賞味期限管理のモジュールに記載しています。
表が5行目から（項目名は4行目)なので対象が5行目からとなっています。
locateには他の表のデータをループして参照し、合致したデータをcaells(locate, 1)に書き込んでいます。書き込んだ後にlocate+1してますので、最終的にlocate-1が最終行になります。(デフォルトは当然5)
データはきちんと記載されているので問題ないかと思います。

add Key:=Range("D5")~ とすべきですかね？

お礼日時：2021/04/03 17:50