excelの他シートのソート方法

質問

VBA初心者です。
excelの他シートのソート方法についての質問です
同一ブック内に作業シートとDATAシートがあり、作業シートを常にアクティブ
にしておいて、DATAシートは作業者にはシークレットとしたい。
この条件の中作業シートには入力規則のリストにより語句を選択する事とし
リストの語句データーはDATAシート（AA列・AC列・・・）より引用しています。
隣接するAB列・AD列には語句を選択した回数を格納カウンターとして利用
そのカウンターを利用して、作業シートから選択頻度の高い順にソートをか
けたいと考え以下のコードを記述し実行したところ”アプリケーション実行時
エラーが出ます。
DATAシートからの実行では問題なく走りますが、DATAはシークレットとした
いところから、解決方法が分からず悩んでいます。
どなたか、ご教示の程宜しくお願いします。

☆以下記述コード
Sub Listsort()
Dim lCol As Long
Dim myRng As Range
Dim Key As String
For i = 27 To 37 Step 2
Key = Sheets("DATA").Cells(2, i + 1)
With Worksheets("DATA")
yline = Cells(Rows.Count, i).End(xlUp).Row
Set myRng = .Range(Cells(2, i), Cells(yline - 3, i + 1))※補足
myRng.Sort _
Key1:=.Range(Key), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom
End With
Next
End Sub
※Cells(yline - 3, i + 1))-3はリスト内の３項目は最終行に固定したいため

回答No.2

myRange · ベストアンサー

>エラー行は、
>Set myRng = Sheets("DATA").Range(Cells(2, i), Cells(yline - 3, i + 1))

Cells(2, i)とCells(yline - 3, i + 1)の前に、"."ドットが必要です。

それからコードの書き方ですが、
With Sheets("DATA")をもっと前にもってていった方がシンプルです。

'------------------------------------------
Sub Listsort()
　Dim lCol As Long
　Dim myRng As Range
　Dim Key As String

　For i = 27 To 37 Step 2
　　　With Worksheets("DATA")
　　　　　Key = .Cells(2, i + 1).Value
　　　　　yline = .Cells(Rows.Count, i).End(xlUp).Row

　　　　　Set myRng = .Range(.Cells(2, i), .Cells(yline - 3, i + 1))

　　　　　myRng.Sort _
　　　　　　　Key1:=.Range(Key), Order1:=xlAscending, _
　　　　　　　Header:=xlYes, Orientation:=xlTopToBottom
　　　End With
　Next i
End Sub
'----------------------------------------------------

以上です。

お礼

bin-chanさん
解決です、有難うございました。
昨日からまる１日悩んでいて、今日は諦めかけていたところです。
救世主が現れた様でとても助かりました。
ほんとうに有難うございました。

回答No.1

bin-chan

> 実行したところ”アプリケーション実行時エラーが出ます。
どの行で発生しますか？

EXCELのバージョンは何ですか？

補足

早速のコメントありがとうございます。
バージョンはEXCEL2007です。
エラー内容は正しくは”アプリケーション定義またはオブジェクト定義の
エラーです。”です
エラー行は、Set myRng = Sheets("DATA").Range(Cells(2, i), Cells(yline - 3, i + 1))で”myRng”で内容は”nothing”となっています。
前コーディングですと”yline”の内容は正しくないことが分かったので現在は以下の
コーディングとしました。シートの指定の方法がいけないのでしょうか？
宜しくお願いします。

Sub Listsort()
Dim lCol As Long
Dim myRng As Range
Dim Key As String
For i = 27 To 37 Step 2
Key = Sheets("DATA").Cells(2, i + 1).Value
yline = Sheets("DATA").Cells(Rows.Count, i).End(xlUp).Row
With Worksheets("DATA")
Set myRng = Sheets("DATA").Range(Cells(2, i), Cells(yline - 3, i + 1))
myRng.Sort _
Key1:=.Range(Key), _
Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom
End With
Next
End Sub