こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

vbaでの正規表現について教えてください

「セルに入ってる値が数値ならA列からE列まで赤色にする」
を正規表現で行いたいのですがよくわかりません。

http://officetanaka.net/excel/vba/tips/tips38.htm
を参考にしているのですが
Sub Sample1()
Dim RE, strPattern As String, r As Range
Set RE = CreateObject("VBScript.RegExp")
strPattern = "SUM\("
With RE
.Pattern = strPattern ''検索パターンを設定
.IgnoreCase = True ''大文字と小文字を区別しない
.Global = True ''文字列全体を検索
For Each r In ActiveSheet.UsedRange
If .test(r.Formula) Then r.Interior.ColorIndex = 3
Next r
End With
Set RE = Nothing
End Sub
を、どう改造すれば、私のやりたい事になるのでしょうか?

【1】
まずstrPattern で、「数値ならば」はどうやればいいでしょうか?

【2】
次にtest(r.Formula)は、数式だからFormulaを使ってるのですよね?
数値を検索する場合はFormulaを何に変えればいいでしょうか?

【3】
最後に、 r.Interior.ColorIndex = 3はそのセルだけの色を変えるのですよね?
A列からE列までにするにはどうすればいいでしょうか?

例えば
A3セルに「1」が、A6セルに「3」が入っていたら、
A列からE列まで赤色にしたいです。

投稿日時 - 2013-01-13 17:47:08

QNo.7889985

困ってます

質問者が選んだベストアンサー

Office TANAKA で例示されているコードは
>アクティブシート内でSUM関数を使っているセルの背景を赤色に塗りつぶ
すコードです。

>「セルに入ってる値が数値ならA列からE列まで赤色にする」
ということでしたら、

Sub Sample2()
  Dim r As Range
  For Each r In Application.Intersect(Columns("A"), ActiveSheet.UsedRange)
    If IsNumeric(r) Then r.Resize(, 5).Interior.ColorIndex = 3
  Next r
End Sub

みたいなことでできるので、わざわざ「正規表現」を使う必要はありません。

 しかし、敢えて
>を正規表現で行いたい
ということになると

Sub Sample3()
  Dim RE, strPattern As String, r As Range
  Set RE = CreateObject("VBScript.RegExp")
  strPattern = "^[0-9]*$"
  With RE
    .Pattern = strPattern    ''検索パターンを設定
    For Each r In Application.Intersect(Columns("A"), ActiveSheet.UsedRange)
      If .Test(r.Formula) Then r.Resize(, 5).Interior.ColorIndex = 3
    Next r
  End With
  Set RE = Nothing
End Sub

のようなことになります。
 検索するのは「数字」ですから大文字も小文字もありませんから、
.IgnoreCase = True
は不要です。また、「^」は「行頭一致」、「$」は「行末一致」を意味しますので、当然「文字列全体を検索」する指定になりますので、
.Global = True
も不要ではないかと存じます。


>test(r.Formula)は、数式だからFormulaを使ってるのですよね?
>数値を検索する場合はFormulaを何に変えればいいでしょうか?
 一概に「数値ならば」と言われても、
a)123
b)'123
c)="1" & "23"
d)=100+23
などいろいろとあります。
 「Sample3」のように「r.Formula」とした場合は、a・bの場合のみになりますが、「r.Value」あるいは「r.Text」とした場合は、a~dのすべてが対象になります。


ということで、
【1】
strPattern = "^[0-9]*$"

【2】
検索の対象により、「r.Formula」または「r.Value」、「r.Text」

【3】
r.Resize(, 5).Interior.ColorIndex = 3
ただし、
>例えば
>A3セルに「1」が、A6セルに「3」が入っていたら、
とお書きですので、検索の対象がA列の場合です。

 実際には、「検索するのがA列で、色づけするのがC~E列」という場合は
r.Offset(, 3).Resize(, 3).Interior.ColorIndex = 3
「検索するのがF列で、色づけするのがB~C列」という場合は
For Each r In Application.Intersect(Columns("F"), ActiveSheet.UsedRange)
If .Test(r.Text) Then r.Offset(, -4).Resize(, 2).Interior.ColorIndex = 3
みたいな感じになります。

投稿日時 - 2013-01-13 20:56:05

お礼

ありがとうございました。

投稿日時 - 2013-01-19 10:44:54

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

こんにちは。

あまり、田中享氏の所を真に受けないほうがよいかもしれませんね。
彼のサイトは、テクニックの披露であっても、現実的?なもの、最適という観点からは、ずれているものが多いような気がします。

まず、「数値ならば」
"^[0-9]*$"
は、単独で読むと、空の文字列も該当することになりますから、
"^[0-9]+" 
または、
"^\d+"
のほうがよいと思います。「*」は、0回以上マッチ, 「+」 は、1回以上マッチです。\dは数値

また、私は、実務では、$は明示的に用いることが多いので、今回のような場合は使いません。Excelの場合は、概ね、問題はないはずですが、行末には必ずしも、予想した文字列が来ていないことも多くあります。VBScriptの正規表現では、\n,\rなどの制御文字も関係するからだと思います。

正規表現は、複雑な文字パターンを検索したりすることに使うことがあっても、今回は、単純な数値などには用いません。あくまでも、正規表現の練習だと解釈して書きます。

>次にtest(r.Formula)は、数式だからFormulaを使ってるのですよね?
いいえ、数式でなくても、文字列でも検知します。Formulaは、数式を文字列にするために使います。もし数式だとするなら、その前に、If r.HasFormula としなければなりません。

後は、コードで示します。

'//
Sub TestRegObject()
'宣言(Dim)の横書きは、VBA/VB6ではなるべくしないこと
Dim objRe As Object 'Variant にはしない
Dim strPattern As String
Dim r As Range
Set objRe = CreateObject("VBScript.RegExp")
 strPattern = ""
  With objRe
   '数字を探すなら、Pattern以外はいらないことは既に述べられていますので、ブロックアウトだけにしておきます。
    .Pattern = strPattern    ''検索パターンを設定
    '.IgnoreCase = True     ''大文字と小文字を区別しない
    '.Global = True         ''文字列全体を検索
    'A列の検索
    For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp))
      If r.Value <> "" Then
        'A~Eまで
        If .Test(r.Value) Then r.Resize(, 5).Interior.ColorIndex = 3
      End If
    Next r
  End With
  Set objRe = Nothing
End Sub

'//
'普通は以下で済みます。ワークシート関数、IsNumberを用います。
Sub Sample4()
 Dim r As Range
 For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp))
  If r.Value <> "" Then
  If Application.IsNumber(r.Value) Then r.Resize(, 5).Interior.ColorIndex = 3
  End If
 Next r
End Sub


'//
'>A3セルに「1」が、A6セルに「3」が入っていたら、
'>A列からE列まで赤色にしたいです。
'表現としては、3行目から6行目までを赤にしたいとも取れます。

Sub Sample5()
 Dim r As Range
 Dim flg As Boolean
 Dim r1 As Range
 For Each r In Range("A1", Cells(Rows.Count, 1).End(xlUp))
  If r.Value <> "" Then
   If Application.IsNumber(r.Value) Then
    If flg = False Then
     Set r1 = r
     flg = True
    Else
     Range(r1, r).Resize(, 5).Interior.ColorIndex = 3
     flg = False
    End If
   End If
  End If
 Next r
End Sub

投稿日時 - 2013-01-14 11:02:08

お礼

ありがとうございました。

投稿日時 - 2013-01-19 10:44:55

あなたにオススメの質問