Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
条件付き書式で左詰めインデントを設定したい
投稿日時: 20/01/10 00:53:38
投稿者: kosanlove

Excelのバージョンは Office365です。
よろしくお願いいたします。
 
条件付き書式ではインデントの指定ができないので
VBAで可能では?とチャレンジしました。
 
A列の値の先頭文字が【77】で始まる場合に 左詰めインデントを設定したい。
マクロの記録で条件付き書式でA列の文字列が77**で始まる色を付け
別セルを選択し左詰めインデントを指定しました。
色を付けた部分の文章と別のセルの左詰めインデントのそれらしい書式の部分を
コピペし下記の構文としました。
 

Sub Macro1()
    Columns("A:A").Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="77**", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Interior
        .IndentLevel = 1
        .HorizontalAlignment = xlLeft
    End With
End Sub

 
マクロを実行すると下記のエラーが発生しました。
 
実行時エラー '438':
オブジェクトは、このプロパティまたはメソッドをサポートしていません。
 
 
デバックを選択すると
下記の部分が黄色になります。
        .IndentLevel = 1

 
試しに下記を削除しても、次は下記の部分が黄色になりました。
        .HorizontalAlignment = xlLeft

 
どのように修正すればよいのでしょうか?
よろしくお願いいたします。

回答
投稿日時: 20/01/10 10:00:15
投稿者: WinArrow
投稿者のウェブサイトに移動

セルの書式設定のダイアログの中にある「配置」が
条件付き書式の書式設定のダイアログの中には見当たらないと思います。
 
VBAだからできるということではないので、仕様と考えた方がよいでしょう。

回答
投稿日時: 20/01/10 11:58:17
投稿者: mattuwan44

手動で出来ないことは、マクロでも基本できません。
 
マクロは、
 
特定の操作手順を自動化する → 標準で付いてない機能を自作する
 
というようなことをしたいときに使用します。
 
なので今回の件では、
手動で、セルを見て回って条件に合うセルを見つけて、
左詰めインデントを設定する作業を、マクロで自動化することになります。
そして、値が変わった時は再度マクロを実行するような運用になると思います。
 

Sub test()
    Const cstKey As String = "77*"
    Dim Rng As Range

    With ActiveSheet.UsedRange.Columns(1)
        .HorizontalAlignment = xlGeneral
        .IndentLevel = 0
        .Copy

        With .Worksheet.Next.Range(.Address)
            .PasteSpecial xlPasteValues
            .Replace What:=cstKey, Replacement:=""
            Set Rng = .SpecialCells(xlCellTypeBlanks)
            .ClearContents
        End With
    End With
    
    With ActiveSheet.Range(Rng.Address)
        .HorizontalAlignment = xlLeft
        .IndentLevel = 1
    End With
End Sub

 
セルの検索を横着したので、
真面目にしないと不具合が出ると思います。参考になれば。

回答
投稿日時: 20/01/10 13:27:28
投稿者: んなっと

表示形式 *[繰り返したい文字] で、
セルの幅いっぱいに[繰り返したい文字]が埋まります。
今回は[半角スペース]を利用しましょう。
VBAは不要です。
 
VBAで設定するなら...
 
  With Selection.FormatConditions.Add(Type:=xlTextString, _
        String:="77", TextOperator:=xlBeginsWith)
      .SetFirstPriority
      .NumberFormat = "@* "
  End With

回答
投稿日時: 20/01/10 14:11:38
投稿者: WinArrow
投稿者のウェブサイトに移動

>A列の値の先頭文字が【77】で始まる場合に 左詰めインデントを設定したい。
 
インデントとか左寄せ
ということではなく、
単純に「文字列」に設定すればいかがでしょうか?
VBA不要です。
 
条件式で
=LEFT(A1,2)="77"
 
書式は、表示形式を文字列に設定。
他の書式もあれば一緒に
 
 

投稿日時: 20/01/10 18:56:22
投稿者: kosanlove

みなさま、沢山のコメントを頂きありがとうございます。
 
それぞれを試してみましたが
思うような動きにはなりませんでした。
 
下記のピボットテーブルを参照しているデータであることを
説明していなかったという点が問題なのだと思います。
 
シート【あ】    元となるデータがあるシート
 A列        数字6桁+半角スペース+任意文字(物件名)
 
シート【あ】A列の 数字6桁のルールが下記です。
1911**        先頭2文字は西暦下2桁(2019年の場合)の中央2文字が11
1999**        先頭2文字は西暦下2桁(2019年の場合)の中央2文字が99
88****        先頭2文字88
00****        先頭2文字00
77****        先頭2文字77 数字6桁(000007)の子コード的な扱い
99****        先頭2文字99
 
シート【い】    元となるデータ【あ】をピボットテーブルとして集計しているシート
        ピボットテーブルで集計した後に 数字6桁のルールで並べ替えをし
        77****のコードがあるデータをインデントしたい。
 
シート【う】    ピボットテーブル集計の結果を参照しているシート
 A列        条件付き書式で下記の6桁コードを6色で色付けし
        下記のルールを色で並替えています
        6色の色付けの状態では印刷しない別の書式としたいので再度別シートへ
 
シート【え】    シート【う】を参照しているシート
 A列        このA列で77****    は数字6桁(000007)の子コード的な扱いなので
        77****のコードがあるデータをインデントしたい。
 
 
コード自体のルール化に問題ということもあるのでしょうが
ルール化を変更するのが困難であり
ピボットにしたあとに手動で並べ替えが可能なのは理解していますが
そこを改定したいということで色々検討していました。
 
昨日の投稿後から 色々試してみました。
シート【え】    シート【う】を参照しているシート をアクティブにしている状態で
下記のマクロで "77****" にインデントが設定されました。
 

Sub Sample1()
    Dim Rng As Range
    Set Rng = Cells.Find(What:="77****", LookIn:=xlValues, LookAt:=xlWhole)
    If Not Rng Is Nothing Then
        Rng.IndentLevel = 1
    End If
End Sub

 
 
 
色々なコメント
ありがとうございました。