Excel VBA質問箱 IV

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

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


3935 / 13644 ツリー ←次へ | 前へ→

【59095】得意先別の並べ替え おしりかじり虫 08/11/28(金) 11:27 質問[未読]
【59097】Re:得意先別の並べ替え Jaka 08/11/28(金) 12:57 発言[未読]
【59098】??? おしりかじり虫 08/11/28(金) 13:25 質問[未読]
【59099】Re:??? Jaka 08/11/28(金) 13:35 発言[未読]
【59101】それだとあまり良くないんです。 おしりかじり虫 08/11/28(金) 15:38 質問[未読]
【59102】Re:それだとあまり良くないんです。 Jaka 08/11/28(金) 15:51 発言[未読]
【59107】Re:それだとあまり良くないんです。 Hirofumi 08/11/28(金) 18:24 発言[未読]
【59112】それもあまりよくないんです。 おしりかじり虫 08/11/29(土) 8:41 質問[未読]
【59113】Re:得意先別の並べ替え かみちゃん 08/11/29(土) 9:44 発言[未読]
【59381】解決しました!(条件付きで) おしりかじり虫 08/12/9(火) 11:41 お礼[未読]

【59095】得意先別の並べ替え
質問  おしりかじり虫  - 08/11/28(金) 11:27 -

引用なし
パスワード
   お世話になります。
VBAで得意先別に売上データを並べ変えたいと思っています。
(実際は得意先別>売上日昇順>回収日昇順の3つの条件)

 Sheets("売上T(売掛金用)").Select
 Worksheets("売上T(売掛金用)").Sort.SortFields.Clear
 Worksheets("売上T(売掛金用)").Sort.SortFields.Add Key:= _
  Range(Range("D2"), Range("D2").End(xlDown)), _
  SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
  "(株)××,(株)○○,(株)△▽,・・・(数十件の顧客名)・・・" _
  , DataOption:=xlSortNormal

 With Worksheets("売上T(売掛金用)").Sort
   .SetRange Range(Range("H1"), Range("A1").End(xlDown))
   .Header = xlYes
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
 End With

上記でやってみると並べ替えができるのですが、得意先も社名変更があったり
付き合いが無くなったり、もちろん新規ができたりするので
あるセル範囲を指定して、そのデータを元に並べ替えしてくれるもの
を作りたいです。そこで、

 Sheets("売上T(売掛金用)").Select
 Worksheets("売上T(売掛金用)").Sort.SortFields.Clear
 Worksheets("売上T(売掛金用)").Sort.SortFields.Add Key:= _
  Range(Range("D2"), Range("D2").End(xlDown)), _
  SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
  sheets("得意先T").Range(Range("A2"), Range("A2").End(xlDown)) _
  , DataOption:=xlSortNormal

 With Worksheets("売上T(売掛金用)").Sort
   .SetRange Range(Range("H1"), Range("A1").End(xlDown))
   .Header = xlYes
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
 End With

上記のような文を作ってみたのですがうまくいきません。
いろいろ調べてみたのですが、直接並べ替えるデータをVBAに書き込む以外の
方法が見つかりませんでした。

解る方いらっしゃいましたら、ご指導ください。

【59097】Re:得意先別の並べ替え
発言  Jaka  - 08/11/28(金) 12:57 -

引用なし
パスワード
   範囲を指定してやれば良いです。

Worksheets("売上T(売掛金用)").Range(XX,XX).Sort・・・・

ヘッダーなどのあるなしも忘れずに

【59098】???
質問  おしりかじり虫  - 08/11/28(金) 13:25 -

引用なし
パスワード
   ▼Jaka さん:
>範囲を指定してやれば良いです。
>
>Worksheets("売上T(売掛金用)").Range(XX,XX).Sort・・・・
>
>ヘッダーなどのあるなしも忘れずに

説明不足で、申し訳ありません。

売上Tシートにある売上データを得意先Tシートの得意先名順に並べ変えたい。

<売上Tデータ>
得意先名 売上日 売上金額
○○電工 11/20  21000円 
××電気 11/25  30000円
△△商事 11/26  90000円

<得意先Tデータ>
得意先名
××電気
○○電工
△△商事

得意先Tデータ順に並び変えるため
Sortの結果

<売上Tデータ>
得意先名 売上日 売上金額
××電気 11/25  30000円
○○電工 11/20  21000円 
△△商事 11/26  90000円

となる。

得意先Tデータを追加・変更したら変更後のデータで並べ変えられる
そんなものを作りたいのですが。

【59099】Re:???
発言  Jaka  - 08/11/28(金) 13:35 -

引用なし
パスワード
   ▼おしりかじり虫 さん:
><得意先Tデータ>
>得意先名
>××電気
>○○電工
>△△商事
この並び順を
ツール → オプション → ユーザー設定リストに
作ってみてください。

【59101】それだとあまり良くないんです。
質問  おしりかじり虫  - 08/11/28(金) 15:38 -

引用なし
パスワード
   ▼Jaka さん:
>▼おしりかじり虫 さん:
>><得意先Tデータ>
>>得意先名
>>××電気
>>○○電工
>>△△商事
>この並び順を
>ツール → オプション → ユーザー設定リストに
>作ってみてください。

得意先は数十件あり、増えたり減ったりしますから
そのたびにユーザー設定リストを直さなければならないです。

当然、得意先は締め日など条件があり、表を作り管理してます。
(得意先条件表というのがあります)
得意先条件表をVBAで得意先のみ一列にならべ得意先Tを作ります。

いくつもの得意先データを手で直すのは間違いの元ですので
一つの表を変更するだけですべてに反映させたいのです。

あくまでシート(得意先T)が変われば、その表を元にして
並び順が決定するというものをVBAで作りたいです。

一番最初に記載したマクロを変えることでシートから
並び順データを呼び出すことはできないでしょうか?

【59102】Re:それだとあまり良くないんです。
発言  Jaka  - 08/11/28(金) 15:51 -

引用なし
パスワード
   う〜ん。
となると、作業列使うとか。
番号は、エクセル関数をつかっても良いだろうし。

>得意先は数十件あり、増えたり減ったりしますから
減る分にはソートの順番は変わりませんけど。

設定リストはマクロでも出来そうですけどね。
やった事無いけど.....。

【59107】Re:それだとあまり良くないんです。
発言  Hirofumi  - 08/11/28(金) 18:24 -

引用なし
パスワード
   得意先に並べたい順番にコード振っちゃたら?
コードも間に入る可能性が有るから、詰めた連番じゃなくて
10飛びとか、100飛びで振ったら如何ですか?

【59112】それもあまりよくないんです。
質問  おしりかじり虫  - 08/11/29(土) 8:41 -

引用なし
パスワード
   ▼Hirofumi さん:
>得意先に並べたい順番にコード振っちゃたら?
>コードも間に入る可能性が有るから、詰めた連番じゃなくて
>10飛びとか、100飛びで振ったら如何ですか?

設計?する段階だったら良かったんですが・・・
売上データがもうかなりあり、それらに得意先番号を振りなおす作業や
得意先番号列を増やす分、今使っているVBAたちを見直したりと
いろいろ面倒になるため、Sort命令だけでなんとかしたい
というのが本音なのですが(T_T)

【59113】Re:得意先別の並べ替え
発言  かみちゃん E-MAIL  - 08/11/29(土) 9:44 -

引用なし
パスワード
   こんにちは。かみちゃん です。

横から失礼します。

> Sort命令だけでなんとかしたい

>>><得意先Tデータ>
>>>得意先名
>>>××電気
>>>○○電工
>>>△△商事
>>この並び順を
>>ツール → オプション → ユーザー設定リストに
>>作ってみてください。
>
>得意先は数十件あり、増えたり減ったりしますから
>そのたびにユーザー設定リストを直さなければならないです。

ユーザー設定リストの設定もマクロで操作すればいかがでしょうか?
また、特定のユーザー設定リストによる並べ替えならできるということを確認されていますか?
確認ができれば、後は、ユーザー設定リストの設定をその都度するには、どうした
らいいかということがポイントになると思います。

他の掲示板の過去ログで申し訳ありませんが、以下のようなスレッドも参考になるかと思います。
http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+200810/08100129.txt
http://park7.wakwak.com/~efc21/cgi-bin/exqalounge.cgi?print+200810/08100164.txt
http://itpro.nikkeibp.co.jp/article/COLUMN/20080716/310951/

ただし、ユーザー設定リストは
> リスト項目の規則として1つのリスト全部で2000文字以内
だそうです。確認できていないのですが・・・

【59381】解決しました!(条件付きで)
お礼  おしりかじり虫 E-MAIL  - 08/12/9(火) 11:41 -

引用なし
パスワード
   ▼かみちゃん さん:
返事が遅くなり本当に申し訳ありません。
かみちゃん様に教えていただいたサイトは大変参考になりました。
同じようなことを考える方はやっぱりいるんですね。
が、いくつか問題が起こりそれを考えているうちに時間が経ってしまいました。
教えて頂いたものを参考に自分の目的に合うように作ったのが次のプロシージャ
です。(私では理解できないところがあったので自分のレベルに合わせた)

Sub 行の並べ替え()

 Dim WS1 As Worksheet
 Dim X As Long
 Dim TList, DList As Variant
 
 Set WS1 = Worksheets("台帳T")
 
 '得意先をユーザー設定リストに追加 
 Sheets("得意先T").Select
 TList = Range(Range("B2"), Range("B2").End(xlDown))
 Application.AddCustomList ListArray:=TList
 X = Application.GetCustomListNum(TList)
 'ダミーユーザー設定リストを追加
 DList = Array("売上", "値引き売上", "入金", "相殺", "前受金")
 Application.AddCustomList DList
 
 WS1.Select
 WS1.Sort.SortFields.Clear
 ' 得意先別で並べ替え(優先1)
 WS1.Sort.SortFields.Add Key:= _
  Range(Range("A2"), Range("A2").End(xlDown)), _
  SortOn:=xlSortOnValues, Order:=xlAscending, _
  CustomOrder:=X, _ ※2 X+1で指定すると何故かエラーになる
  DataOption:=xlSortNormal
 ' 日付昇順で並べ替え(優先2)
 WS1.Sort.SortFields.Add Key:= _
  Range(Range("B2"), Range("B2").End(xlDown)), _
  SortOn:=xlSortOnValues, Order:=xlAscending, _
  DataOption:=xlSortNormal
 ' 種目別で並べ替え(優先3)
 WS1.Sort.SortFields.Add Key:= _
  Range(Range("C2"), Range("C2").End(xlDown)), _
  SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
  "売上,値引き売上,入金,相殺,前受金", DataOption:=xlSortNormal
 ' 前期繰越を一番前に(優先4)
 WS1.Sort.SortFields.Add Key:= _
  Range(Range("E2"), Range("E2").End(xlDown)), _
  SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
  "前 期 繰 越", DataOption:=xlSortNormal

 With WS1.Sort
   .SetRange Range(Range("H1"), Range("A1").End(xlDown))
   .Header = xlYes
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
 End With
 
 'これを入れないと次回使用時にファイルが壊れる
 WS1.Sort.SortFields.Clear
 
 'ユーザー設定リストの削除
 Application.DeleteCustomList ListNum:=X
 'ダミーユーザー設定リストの削除
 Application.DeleteCustomList ListNum:=X
 
End Sub

一つ目は私の使っているのはExcel2007なので2007用にプログラムを変えました。
2003以前の並べ替えメゾットは私ではうまくいかなかった…。

二つ目は、理由は解りませんが使用したいユーザー設定リストがリスト欄の
一番下にあるとエラーが発生してしまいます。
結局これは解決できなかったので、ダミーのユーザー設定リストを一つ追加し
X番目のリストを指定することでとりあえず動かすことができました。 ※2

>ただし、ユーザー設定リストは
>> リスト項目の規則として1つのリスト全部で2000文字以内
>だそうです。確認できていないのですが・・・
Len関数で数えたところ600文字くらいでしたのでまだ余裕がありそうです。

目的の処理をすることはできましたが、2003以前のEXCELでは使用できないですし
ダミーユーザー設定リストを作るという余分なこともしてますが
とりあえず解決ということにさせてもらいます。
みなさん、ありがとうございました。

3935 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free