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

解決済みの質問

If Like Then条件式可変の場合の処理

VBAにてSheet1の指定された番地にあるセルの値がSheet2のセル範囲にない場合はセルに色をつけると言うマクロを組んでいます。

Sub test()

Dim i,z As Integer
Dim WS As Worksheet
Dim LastRow As Integer
Dim Word1,Word2,Word3 As String

Set WS = Worksheets("Sheet1")

Word1 = WS.Cells(1,16).Value
Word2 = WS.Cells(1,17).Value
Word3 = WS.Cells(1,18).Value

with Worksheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

For i = 3 To LastRow
For z = 6 To 9
If .Cells(i,z).Value Like "*" & Word1 & "*" Or _
.Cells(i,z).Value Like "*" & Word2 & "*" Or _
.Cells(i,z).Value Like "*" & Word3 & "*" Then

Else
.Cells(i,z).Interior.ColorIndex = 6

End If
Next
Next

End Sub

上記のマクロで変数Word1,2,3が1つだけの場合もあれば
Word10まである場合も有り式を変更する必要が有ります。。
この場合、If Like 変数 Or の部分をフレキシブルに対応させる為にはどの様な式を書けば良いでしょうか(ToT)?

申し訳ありませんがご教授下さい(ToT)
宜しくお願い致します。

投稿日時 - 2013-07-22 09:45:25

QNo.8186605

困ってます

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

えと・・つまり、
・Sheet2の6~9列(F:I列)、3行目以降のセルに対し
・Sheet1のA16以降(不定数)の値(※)と比較して
・「上記(※)をどれも含まない」ときは(Sheet2の対象セルに)色を付ける
と言う処理でしょうか。


マクロを使って、条件範囲(Sheet1)を可変にして、
全てを舐めていく・・と言う考え方がわかりやすいかもしれません。
例えば

Sub Sample()
Dim TRange As Range, TCell As Range
Dim CRange As Range, CCell As Range

    With Sheets("Sheet2")
        Set TRange = .Range("F3:I" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With 

    With Sheets("Sheet1")
        Set CRange = .Range("A16:A" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With

    For Each TCell In TRange
        For Each Ccell In CRange
            If InStr(TCell, Ccell) Then
                TCell.Interior.ColorIndex = 6
            End If
        Next
    Next
End Sub

こんな具合。
積極的な手段とは言えませんが、参考までに。



ところで、変数型の宣言について。

Dim i,z As Integer
Dim WS As Worksheet
Dim LastRow As Integer

行数をInteger(整数型)で宣言していらっしゃいますが、
整数型は「-32,768~+32,767」の範囲にしか対応できません。
もちろん好みの問題ですが、行数が増えてしまうとオーバーフローしますので
あわてずに対応できるようにしてあげてください。


もうひとつ。

Dim Word1,Word2,Word3 As String

これだと、変数「Word3」については「文字列型」で正確に宣言できますが、
変数「Word1,Word2」については「型を省略した」とみなされ、
「Variant型」に設定されてしまいます。
ま、ヴァリアント型は何でも来いの型なので大きく問題にはなりませんが、
ちょぃと複雑なマクロを組み始めると「速度」に関わることがありますので
面倒でも
  Dim Word1 As String, Word2 As String, Word3 As String
あるいは
  Dim i As Integer, z As Integer
と、それぞれに対して宣言する癖をつけた方が良いですよ。

以上、お節介ながら。

投稿日時 - 2013-07-22 12:43:12

お礼

無事解決いたしました!!
ありがとうございました!!

変数の宣言の件すごく勉強になりました。
独学でVBAを勉強しているのですがこういう仕組?的な所は
あまりわからず指摘して頂けるとすごく為になります!!

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

投稿日時 - 2013-07-22 19:31:40

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

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

回答(3)

Word1~についてはFor Eachで回す。
対象の範囲が一塊ならそのセル範囲を指定すればいいし、離れているならUnionを使う。

また、Likeのところも冗長なのでWorksheetFunction.CountIfを使用したほうがいい。

ただし、ForはUnionで設定した範囲でも順番に取ってくれるけど、CountIfはダメなので、もしSheet2の範囲が一塊じゃない場合は、それぞれの範囲でCountIfを行う必要があります。

本文の例ならセル範囲は連続しているのでUnionのことは気にしなくていいです。

投稿日時 - 2013-07-22 11:36:39

お礼

無事解決いたしました!!
ありがとうございました!!

投稿日時 - 2013-07-27 21:55:49

ANo.1

こんにちは!
せっかくコードをお考えのようですので、余計なお世話になるかもしれませんが
こちらで勝手にコードを作ってみました。
一例です

尚、検索「Word」はSheet1のP1・Q1・R1・・・と1行目に入力してあるとします。
標準モジュールにコピー&ペーストして↓のマクロを試してみてください。


Sub Sample1()
Dim i As Long, j As Long, k As Long, endCol As Long, myFlg As Boolean
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS2.Range("F:I").Interior.ColorIndex = xlNone
endCol = wS1.Cells(1, Columns.Count).End(xlToLeft).Column
If endCol >= 16 Then
For i = 3 To wS2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 6 To 9
If wS2.Cells(i, j) <> "" Then
myFlg = False
For k = 16 To endCol
If InStr(wS2.Cells(i, j), wS1.Cells(1, k)) > 0 Then
myFlg = True
Exit For
End If
Next k
If myFlg = False Then
wS2.Cells(i, j).Interior.ColorIndex = 6
End If
End If
Next j
Next i
End If
Application.ScreenUpdating = True
End Sub

こんな感じではどうでしょうか?m(_ _)m

投稿日時 - 2013-07-22 11:30:17

お礼

とても参考になりました!
フラグを使用してのVBAの構文はまだ使ったことがないので
参考にさせて頂き精進します!

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

投稿日時 - 2013-07-22 19:33:36

あなたにオススメの質問