Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
6594 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【33289】並び替えについて 平 06/1/7(土) 15:02 質問[未読]
【33295】Re:並び替えについて かみちゃん 06/1/7(土) 18:03 発言[未読]
【33296】Re:並び替えについて ichinose 06/1/7(土) 18:32 発言[未読]
【33373】Re:並び替えについて Hirok 06/1/9(月) 21:36 質問[未読]
【33374】Re:並び替えについて かみちゃん 06/1/9(月) 21:50 発言[未読]
【33375】Re:並び替えについて ichinose 06/1/9(月) 22:16 発言[未読]
【33377】Re:並び替えについて Hirok 06/1/10(火) 10:29 お礼[未読]
【33400】Re:並び替えについて ichinose 06/1/10(火) 19:00 発言[未読]
【33403】Re:並び替えについて Hirok 06/1/10(火) 21:37 お礼[未読]
【33378】Re:並び替えについて 平 06/1/10(火) 11:26 お礼[未読]
【44311】Re:並び替えについて 魚 06/11/12(日) 22:42 質問[未読]
【44312】Re:並び替えについて ichinose 06/11/13(月) 7:29 発言[未読]
【44314】Re:並び替えについて 魚 06/11/13(月) 9:41 質問[未読]
【44349】Re:並び替えについて ichinose 06/11/13(月) 18:23 発言[未読]
【44353】Re:並び替えについて 魚 06/11/13(月) 20:23 お礼[未読]
【33289】並び替えについて
質問  平  - 06/1/7(土) 15:02 -
引用なし
パスワード
   並び替えについてですが、マクロ記録をさせたら下記のようなマクロになりました。

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
　　OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
　　:=xlPinYin, DataOption1:=xlSortNormal

このマクロで文字列・数字などが混ざっているリストを並べ替えすると
なぜか変な並び方となってしまいます。

　並び替え前　　　　　　　並び替え後
　7324 　　　　　　　　　 6028
　7324C　　　　　　　　　 7324
　6028 　　　　　　　　　 16028C
　16028C　　　　　　　　　7324C

これを下記のように並ぶようにするには、どの部分を直せば宜しいのでしょうか。
ご教授願います。

　16028C
　6028
　7324
　7324C

ちなみにエクセルのバージョンは2002です。
【33295】Re:並び替えについて
発言  かみちゃん  - 06/1/7(土) 18:03 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>このマクロで文字列・数字などが混ざっているリストを並べ替えすると
>なぜか変な並び方となってしまいます。

数値→文字列の順で並べ替えが行なわれていますので、別に変な並びではありません。
「数値」を「数字の文字列」にしてしまっていいですか？
【33296】Re:並び替えについて
発言  ichinose  - 06/1/7(土) 18:32 -
引用なし
パスワード
   平さん、かみちゃん さん、こんばんは。
>
並び替え前
　7324 　
　7324C　
　6028 　
　16028C　

かみちゃん さんが
>「数値」を「数字の文字列」にしてしまっていいですか？
とおっしゃっているように文字列として比較（並べ替え）を行わなければなりません。

'=================================================
Sub test2()
　　Dim col As Long 'データが入力されている最右列番号
　　col = Cells(2, Columns.Count).End(xlToLeft).Column
　　Set rng = Range("a2", Cells(Rows.Count, 1).End(xlUp))
　　With rng.Offset(0, col)
　　　 .Formula = "=a2&"""""
　　End With
　　With Range("A2").CurrentRegion
　　　　.Sort Key1:=Cells(2, col + 1), Order1:=xlAscending, Header:=xlNo, _
　　　　OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
　　　　:=xlPinYin
　　End With
　　rng.Offset(0, col).Value = ""
End Sub

細かいエラーチェックはしていませんが・・・。
上記のコードは、
作業列を設けて、Ａ列のデータを文字列化した列をキーにして
並べ替えを行っています。
【33373】Re:並び替えについて
質問  Hirok  - 06/1/9(月) 21:36 -
引用なし
パスワード
   ▼ichinose さん：
横からすみません。
ちょっと教えていただきたいのですが。
平様の並び替えでは項目が書かれていないので、問題なく動作しますが
例えば

　　A　　　　　B　　　　C　　
5　項目１　　項目2
6　　7324　　　56
7　　7324C　　 38　
8　　6028　　　99
9　　16028C　　48

　のような状態の場合は
　With Range("A2").CurrentRegionでは項目1まで含んで
しまいますが、避ける方法を教えていただけませんでしょうか。
【33374】Re:並び替えについて
発言  かみちゃん  - 06/1/9(月) 21:50 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>　With Range("A2").CurrentRegionでは項目1まで含んで
>しまいますが、避ける方法を教えていただけませんでしょうか。

見出し行のあるリストから、見出し行は選択せずに、データ部分だけを選択したい
ということでいいでしょうか？
それであれば、
CurrentRegionプロパティのヘルプを見てください。
使用例とともに載っています。
【33375】Re:並び替えについて
発言  ichinose  - 06/1/9(月) 22:16 -
引用なし
パスワード
   こんばんは。

>ちょっと教えていただきたいのですが。
>平様の並び替えでは項目が書かれていないので、問題なく動作しますが
>例えば
>
>　　A　　　　　B　　　　C　　
>5　項目１　　項目2
>6　　7324　　　56
>7　　7324C　　 38　
>8　　6028　　　99
>9　　16028C　　48
>
>　のような状態の場合は
これは、５行目が項目名で６行目からデータが入っているということですね！！
項目名がある場合はSortメソッドでそのように指定すればよいですよ！！
前投稿のコードをちょっとだけ変更して・・・、

'======================================================
Sub test2()
　　Dim col As Long 'データが入力されている最右列番号
　　col = Cells(5, Columns.Count).End(xlToLeft).Column
　　Set rng = Range("a6", Cells(Rows.Count, 1).End(xlUp))
　　With rng.Offset(0, col)
　　　 .Formula = "=a6&"""""
　　End With
　　With Range("A5").CurrentRegion
　　　　.Sort Key1:=Cells(5, col + 1), Order1:=xlAscending, Header:=xlYes, _
　　　　OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
　　　　:=xlPinYin
　　End With
　　rng.Offset(0, col).Value = ""
End Sub

確認してみて下さい
【33377】Re:並び替えについて
お礼  Hirok  - 06/1/10(火) 10:29 -
引用なし
パスワード
   ▼ichinose さん：

ありがとうございました。Sortメソッドにこのような使い方があることを理解できました。今まで項目行までが含まれてしまい、困ったことがありました。
Headerの意味が分からず、記録させるとxlGuessとでていて意味も分からず、今にいたっています。xlYesを使うと先頭行をタイトルと見なすのですね。

後一点教えていただきたいのですが
With rng.Offset(0, col)
　　　 .Formula = "=a6&"""""
End With
ここの構文で、"=a6""""の意味合いを教えていただけませんでしょうか？
「"」の使い方？が全く分かりません。が、検索で調べられますでしょうか？

また別件ですが、ソートを実行してしまった後、時には元に戻したい(元の配列）時がありますが、そのときは
項目行にNoをつけて連番を作成して、その連番を頼りにしてソートをかけていますが
もっと単純な方法はあるのでしょうか。

▼かみちゃんさん
ご指摘ありがとうございました。
CurrentRegionメソｯドで「見出し行」を外し、データ行のみを対象にする方法がヘルプにありました。具体的にどのように書くと良いか今のコードに入れて使える方法を考えてみたいと思います。これからも宜しくお願いします。
【33378】Re:並び替えについて
お礼  平  - 06/1/10(火) 11:26 -
引用なし
パスワード
   ▼ichinose さん、かみちゃん さん

連絡遅くなりまして申し訳ありません、回答ありがとうございます。
一度教えて頂いたもので試してみます。また不明な事がでた際には
よろしくお願い致します。
【33400】Re:並び替えについて
発言  ichinose  - 06/1/10(火) 19:00 -
引用なし
パスワード
   ▼Hirok さん：
こんばんは。
>
>後一点教えていただきたいのですが
>With rng.Offset(0, col)
>　　　 .Formula = "=a6&"""""
>End With
>ここの構文で、"=a6""""の意味合いを教えていただけませんでしょうか？
>「"」の使い方？が全く分かりません。が、検索で調べられますでしょうか？
まず、新規ブックの標準モジュールに
'================================
Sub test()
　　Range("a1").Value = 123
　　With Range("b1")
　　　 .Formula = "=a1"
　　　 MsgBox TypeName(.Value)
　　　 .Formula = "=a1&"""""
　　　 MsgBox TypeName(.Value)
　　End With
End Sub

これを実行してみて下さい。
最初に　Double　と表示され、次いで　String　と表示されますよね？

セルＢ１に
「=a1&""」という数式を指定すると、
「=a1」という数式では数値だったセルＢ１が
文字列に型変換されるということです。

本題では、文字列のソートをさせるためにこのように細工を施したのです。

で、

.Formula = "=a1&"""""

これですが、"（ダブルコーテーション）は、ＶＢＡでは、
文字列を表わすのにその開始位置と終了位置に付けますよね？

Dim a as string
a="文字列"

というように
では、この「"」を文字列だと認識させる開始位置と終了位置のための「"」ではなく、

文字として「"」を指定する場合は、「""」と指定します。
これは、文法です。覚え事です。


Dim a as string
a="文字列の""です"

というように・・・。

よって、
.Formula = "=a1&"""""

は、「""""」は、文字として「""」が指定されたことになります。

つまり、数式として　=a1&""　が指定された事になるのです。


>
>また別件ですが、ソートを実行してしまった後、時には元に戻したい(元の配列）時がありますが、そのときは
>項目行にNoをつけて連番を作成して、その連番を頼りにしてソートをかけていますが
>もっと単純な方法はあるのでしょうか。

これは、私もこのようにしますけどね！！
【33403】Re:並び替えについて
お礼  Hirok  - 06/1/10(火) 21:37 -
引用なし
パスワード
   ▼ichinose さん：

大変ありがとうございました。
長文で時間をかけてご回答いただき恐縮しています。

いつも親切なご指導ありがとうございます。
今回は、また格別の経験をさせていただいた喜びです。
これからも宜しくご指導のほどお願いいたします。

本当にありがとうございました。
これを何とかマスターして、活用したく思います。
失礼します。
【44311】Re:並び替えについて
質問  魚  - 06/11/12(日) 22:42 -
引用なし
パスワード
   並べ替えについて学んでいます。
教えてください。
こちらでのコードを引用して記述しました。
並べ替えのkey1は日付＋時間です。
応用で、下記★の部分を削除したところ、

『実行エラー'1004'
並べ替えの参照が正しくありません。並べ替えるデータ内にあることと、［最優先されるキー］ボックスが空白でないことを確認してください。』
のエラーがでました。

どうしてでしょうか？教えてください。


>'=================================================
>Sub test2()
>　　Dim col As Long 'データが入力されている最右列番号
>　　col = Cells(2, Columns.Count).End(xlToLeft).Column
>　　'Set rng = Range("a2", Cells(Rows.Count, 1).End(xlUp))　・・・★
>　　'With rng.Offset(0, col)　・・・★
>　　'　 .Formula = "=a2&"""""　・・・★
>　　'End With　・・・★
>　　With Range("A2").CurrentRegion
>　　　　.Sort Key1:=Cells(2, col + 1), Order1:=xlAscending, Header:=xlNo, _
>　　　　OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
>　　　　:=xlPinYin
>　　End With
>　　'rng.Offset(0, col).Value = ""　・・・★
>End Sub
【44312】Re:並び替えについて
発言  ichinose  - 06/11/13(月) 7:29 -
引用なし
パスワード
   ▼魚 さん：
おはようございます。

>並べ替えについて学んでいます。
>教えてください。
>こちらでのコードを引用して記述しました。
>並べ替えのkey1は日付＋時間です。
>応用で、下記★の部分を削除したところ、
>
>『実行エラー'1004'
>並べ替えの参照が正しくありません。並べ替えるデータ内にあることと、［最優先されるキー］ボックスが空白でないことを確認してください。』
>のエラーがでました。
>
>どうしてでしょうか？教えてください。
まず、どんなデータに対して下記のコードを実行したかを
必ず、記述してください。
だって、下記コードの入力データが何なのか分からなければ
見ている方が下のプログラムを実行しても再現できませんよね！！

1.　プログラムの機能説明
2.　入力データの説明（入力データ例を含む）
3.　出力データの説明（出力データ例を含む）

上記の情報は、必須記述項目だというくらいに思っていただいて
構わない　と思いますよ！！


>>'=================================================
>>Sub test2()
>>　　Dim col As Long 'データが入力されている最右列番号
>>　　col = Cells(2, Columns.Count).End(xlToLeft).Column
>>　　'Set rng = Range("a2", Cells(Rows.Count, 1).End(xlUp))　・・・★
>>　　'With rng.Offset(0, col)　・・・★
>>　　'　 .Formula = "=a2&"""""　・・・★
>>　　'End With　・・・★
>>　　With Range("A2").CurrentRegion
　　　　 Ａ列にしかデータがないのであれば？
　　　　　.Sort Key1:=Cells(2, 1), Order1:=xlAscending, Header:=xlNo, _
>>　　　　OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
>>　　　　:=xlPinYin
>>　　End With
>>　　'rng.Offset(0, col).Value = ""　・・・★
>>End Sub

で試してみてください。
【44314】Re:並び替えについて
質問  魚  - 06/11/13(月) 9:41 -
引用なし
パスワード
   条件提示不足で申し訳ありません。

データはＡ〜ＡＰ列×45,000行あります。
それからコードはシンプルにして
With Worksheets(1).Range("A1").CurrentRegion
　　.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
　　　 MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
End With
でうまく動いたように思います。

質問ですが、上記のようにソート範囲の指定をせずとも
Ａ〜ＡＰはきちんとソートされますか？
【44349】Re:並び替えについて
発言  ichinose  - 06/11/13(月) 18:23 -
引用なし
パスワード
   ▼魚 さん：
こんばんは。

>データはＡ〜ＡＰ列×45,000行あります。
45000行もあるデータをExcelで扱ったことがありません。
（このような場合、Access等のデータベースに移行してしまいます）

>それからコードはシンプルにして
>質問ですが、上記のようにソート範囲の指定をせずとも
>Ａ〜ＡＰはきちんとソートされますか？

Worksheets(1).Range("A1").CurrentRegion

と記述していますね！！これは、アクティブセル領域 を返すプロパティです
（アクティブセル領域 については、Helpを参照のこと）。

よって、ソート範囲は指定されています。

>Ａ〜ＡＰはきちんとソートされますか？

CurrentRegionでは、以下のようなデータの場合、正しくソートされません。


新規ブックの標準モジュールに

'===============================================================
Option Explicit
Sub main()
　　Call mk_sample　'サンプルデータの作成
　　MsgBox "sort ready"
　　With Worksheets(1).Range("A1").CurrentRegion
　　　　 .Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
　　　　　　　　 MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
　　　　 End With
End Sub
'========================================================================
Sub mk_sample()
　　With Range("a1:ap1")
　　　 .Formula = "=""項目""&column()"
　　　 .Value = .Value
　　　 With .Offset(1, 0).Resize(30)
　　　　　.Formula = "=int(rand()*1000)+1"
　　　　　.Value = .Value
　　　　　End With
　　　 .Offset(31, 0).EntireRow.ClearContents
　　　 With .Offset(32, 0).Resize(5)
　　　　　.Formula = "=int(rand()*1000)+1"
　　　　　.Value = .Value
　　　　　End With
　　　 End With
end sub


上記のコードをコピーして実行してみてください。


実行すると、サンプルデータをアクティブシートに作成します。

Ａ列〜ＡＰ列にサンプルデータを作成しますが（A1からAP40で1行目は、項目名）、

32行目だけが全て未入力になっています。


このようなデータに対して、

Range("A1").CurrentRegion

で取得するセル範囲は　A1〜AP31であって、A1〜AP40にはなりません。


このようなデータになる可能性がある場合は、

CurrentRegion　で正しくセル範囲を取得できませんから、注意が必要です。
【44353】Re:並び替えについて
お礼  魚  - 06/11/13(月) 20:23 -
引用なし
パスワード
   ▼ichinose さん：
例題まで作成していただいて、
とてもわかり易く理解できました。
.CurrentRegionについては誤解して覚えていたので
そこも気づかせていただいて勉強になりました。
本当にありがとうございました。

ちなみにデータは空白行は存在しないので大丈夫です。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
6594 / 13644 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free