vbaの「実行時エラー'1004':'Range'メソッドは失敗しました: '_Global'オブジェクト」について

Question

前提・実現したいこと

vbaで任意の列を検索し、その列を並び替えたいです。

発生している問題・エラーメッセージ

実行時エラー'1004':
'Range'メソッドは失敗しました: '_Global'オブジェクト

該当のソースコード

Sub 並びかえマクロ()

　　Dim 変更年月日列 As Variant
    変更年月日列 = Range("1:1").Find("変更年月日").Column

' 変更年月日を昇順に並び替え
    
    ActiveWorkbook.Worksheets("指定したシート名").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("指定したシート名").AutoFilter.Sort.SortFields.Add Key:= _
        Range(変更年月日列, 2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("指定したシート名").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End sub

シート構造

列　頭｜列　頭｜列　頭｜列　頭｜列　頭｜列　頭｜列　頭｜列　頭｜列　頭｜
データ｜データ｜データ｜データ｜データ｜データ｜データ｜データ｜データ｜
データ｜データ｜データ｜データ｜データ｜データ｜データ｜データ｜データ｜
データ｜データ｜データ｜データ｜データ｜データ｜データ｜データ｜データ｜

事前に指定した値をvbaで設定→それを１行目で検索→該当する列を並び替える　ということがやりたいです。

Answer 1

記事タイトルのエラーは、以下の行で発生したものと想定して回答します(エラーが発生したのであれば、発生場所を質問に記載した方が回答がされやすいです)。

ActiveWorkbook.Worksheets("指定したシート名").AutoFilter.Sort.SortFields.Add Key:= _
        Range(変更年月日列, 2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

まず、エラーの理由はRangeプロパティの使い方が間違っている、渡している引数が適切でないためです。

Range(変更年月日列, 2)

Range プロパティ (Excel) | Microsoft Docs

Worksheet オブジェクトの Range プロパティは以下の引数の組み合わせしか受け取りません。

• 引数が1個のときは、A1 形式のセルのアドレスの文字列("A1"など)を1個

(Range("A1"), Range("5:5")など)

• 引数が2個のときは、A1 形式のセルのアドレスの文字列、または任意のRangeオブジェクトのいずれかを2個(混ぜて指定してもOK)

(Range("A1", "C3"), Range("A1", Cells.Item(3, 3)), Range(Cells.Item(1), "C3"), Range(Cells.Item(1), Cells.Item(3, 3))など)

そして、質問のRange(変更年月日列, 2)の記述では、変更年月日列がLong、2がIntegerとなり、両方とも整数となっています。

整数は文字列に暗黙的に変換できますが、「A1 形式のセルのアドレスの文字列」にはならないため、上記の組み合わせに当てはまりません。
結果として引数が間違っていることになり、Range プロパティが失敗します。

行・列の番号を指定してセルを取得したいのであれば、Cells.Item(行, 列)を使うのが一般的でしょう。

---

その他気になったところ

親のシートの明示

問題の箇所や、最初のRange("1:1")に関しては、今の記述だと最前面のシートを対象にしているため、親のWorksheetを明示した方が良さそうに思います。

オブジェクト変数の使用

ActiveWorkbook.Worksheets("指定したシート名")と毎回書いていますが、長くて面倒な上に、VBA側の編集補助機能が無効化されてしまいます。

Dim targetWs As Worksheet
Set targetWs = ActiveWorkbook.Worksheets("指定したシート名")

のように変数に入れておくと、以下のように短く記述でき、targetWs以降についても入力補完が使えるようになります。

targetWs.AutoFilter.Sort.SortFields.Clear

Answer 2

ミスにより削除しました。