[[20080513203751]]　『最長文字列検索と空白削除』（万年太郎）　ページの最後に飛ぶ

『最長文字列検索と空白削除』（万年太郎）

 下記のDBをＶＢＡ処理しようと思うのですが、アイデアが浮かびません。
どなたか知恵を貸して下さい。

    A	         B         C
1 部署コード   部署名    担当者
2   1001	      総務部    山田太郎
3   1002	      営業部１  石井恵子
4   1003	      営業部２  石川一、田村一郎　　　　千葉二郎、福島二郎
5   1004	      経理部    伊藤一郎
6   1005	      技術部    山田三郎

 C列に担当者名が書かれており、4行目のみ4名の名前が列記されている他はせいぜい1名
～2名なので4行目のせいでｃ列が長くなってしまいます。
 それを下記の様に処理して短く出来ないかな、と思っています。
 上記はサンプルの為、６行ですが、実際は２００行を超えており最長文字列行を探すのが大変です。
①文字列が一番長い行を自動検索する
②文字列が最も長い行の担当者で空白文字列をカットして”、”をつける
③Columns("C:C").EntireColumn.AutoFit を使用してＣ列を短くする

Window XP,Excel2003

 最終的にはどの様に成ればよいのですか？
 ＞石川一、田村一郎　　　　千葉二郎、福島二郎
 の様に、間にスペースが含まれている行は
 スペースを取り除き「、」に変更
 されれば良いのでしょうか？

 でも、そう言う事なら
 ＞(1)文字列が一番長い行を自動検索する
 必要は無いですよね

 (1)で見つかった「一番長い行」に関してのみ
 スペースが「、」に成れば良いのでしょうか？

 ＞最長文字列行を探すのが大変です。
 最長文字列行は、D列にでも
 LEN関数でC列の文字数を数え
 E1セル等に =MAX(D:D)とすれば
 D列の最大値が求まります。
 D列にオートフィルタをかけて　E1セルと
 等しい値の物のみ表示させれば　簡単に見つかりそうに思います。

 でも、どの様な事をなさりたいのか良く分からないので・・・。
 例えば、A1セルに「石川一、田村一郎　　　　千葉二郎、福島二郎」
 が入っている場合、B1セルに
=SUBSTITUTE(TRIM(A1),"　","、")
 の式を入れると「石川一、田村一郎、千葉二郎、福島二郎」
 の様に表示させることが出来ますが。
（空白が全角スペースの場合です。）

 また、最長文字列行にのみ実行したい場合は
 文字数を数える作業列を作れば良いように思います。

 (HANA)

 マクロでするなら、こんな感じかな？
 2人以上　スペースがあると判断しています。
　　　　　　(SHIOJII）　　
 Option Explicit

 Sub test()
    Dim r As Range
    Dim myVal As String

    For Each r In Range("C2", Cells(Rows.Count, 3).End(xlUp))
        myVal = Replace(r.Value, " ", "　")
        If InStr(myVal, "　") > 0 Then
           myVal = WorksheetFunction.Trim(myVal)
           r.Value = Replace(myVal, "　", "、")
        End If
    Next
    Columns("C:C").EntireColumn.AutoFit
 End Sub

 HANAさんの数式をそのままで..

 Sub test()
 Dim x As String
 With Range("c2", Range("c" & Rows.Count).End(xlUp))
     x = .Address
     .Value = Evaluate("if(" & x & "<>"""",substitute(trim(" & x & "),""　"","、"),"""")")
     .EntireColumn.AutoFit
 End With
 End Sub
 (seiya)

 HANAさん、SHOJIIさん、seiyaさんありがとうございます。
SHOJIIさんのﾃｽﾄではうまくいったのですが、seiyaさんのでは
.Value = Evaluate("if(" & x & "<>"""",substitute(trim(" & x & "),""　"","、"),"""")")
のところでコンパイルエラーとなりました。記述のどこに問題があるか判らず、悪戦苦闘中です。
それとEVALUATEをEXCEL学校の全文検索で検索したら、seiyaさんが投稿した
[[20070212134849]] Evaluate Method の研究が出てきましたが、この応用ですか？
Evaluate,と言うメソッドは初めて見ました・・・・ @__@ (万年太郎）

 " が抜けていました。
      .Value = Evaluate("if(" & x & "<>"""",substitute(trim(" & x & "),""　"",""、""),"""")")
                                                                             ^^^^^^^^

 に変更してください。
 (seiya)

 seiyaさん、うまくいきました。ところで、HANAさんの数式をそのままで..というコメントは
HANAさんの投稿の中にある　=SUBSTITUTE(TRIM(A1),"　","、")　の部分を

 .Value = Evaluate("if(" & x & "<>"""",substitute(trim(" & x & "),""　"",""、""),"""")")

 に記述を変えただけですか？もし、そうであれば" & x & "　の部分の意味を説明頂けませんか？
（><)　万年太郎

 HANA さんの数式は
 =SUBSTITUTE(TRIM(A1),"　","、")
 ですよね?
 vba で上記数式をセルに入力する際には、
 "=SUBSTITUTE(TRIM(A1),""　"",""、"")"
 のようになります。

 変数x には B2:Bの最終列のアドレスが格納されています。
 例えば B2:B100　として
 substitute(trim(B2:B100),""　"",""、""),"""")
 アドレス部分をxに置き換えると
 substitute(trim(" & x & "),""　"",""、""),"""")
 になります。
 数式を利用してその結果をEvaluateメソッドで配列を作成する場合は、まず仮想配列を作成する必要があります。
 (関数を使用しない、または使用関数が配列関数であればその必要はありません)
 その部分が
 If(B2:B100<>"",
 です。
 If(Rows(B2:B100)
 でもOKです。
 その配列に数式の結果を挿入しています。
 参考にしてください。
 (seiya)

 なるほど、よーわかりました。Seiya先生、ありがとうございます！
m(__)m m(__)m (万年太郎）

コメント返信：

[ 一覧(最新更新順) ]

[[20080513203751]] 『最長文字列検索と空白削除』（万年太郎） ページの最後に飛ぶ

[[20080513203751]]　『最長文字列検索と空白削除』（万年太郎）　ページの最後に飛ぶ